Can it be done?

I've been trying all day to retrieve a value from a table. This value will determine some outcomes of what my stored procedure does. But for some reason I'm constantly returned a 'false' value whenever I run the following even though I know that the value is true in the corresponding table. Is my syntax wrong or is this even possible:

DECLARE @.IsDuplicate NVARCHAR(5);
SET @.IsDuplicate = (SELECT IsDuplicate FROM User_Notes WHERE FK_UN_UserID = @.FK_UserID AND FK_UN_NoteID = @.NoteID) -- Is it an admin note

the

@.FK_UserID = 3
@.NoteID = 5

the table

FK_UN_UserID : FK_UN_NoteID : IsDuplicate

1 3 False
2 4 True
3 5 True

I need to get the value of IsDuplicate where FK_UN_UserID = 3
and FK_UN_NoteID = 5, the result would give 'true', but its not??

I'm baffledlet me get this straight. you're storing True or False in an NVarChar? Why not a bit?

Anyways... Try this.

IF (SELECT Count(*) FROM User_Notes WHERE FK_UN_UserID = 5 AND FK_UN_NoteID = 3) > 1
BEGIN

Print 'You got dupes buddy'

END

--

IF (SELECT Count(*) FROM User_Notes WHERE FK_UN_UserID = 3 AND FK_UN_NoteID = 5) > 0
BEGIN

Print 'You got your params flipped buddy'

END|||Syntax:

SELECT @.IsDuplicate = IsDuplicate FROM User_Notes WHERE FK_UN_UserID = @.FK_UserID AND FK_UN_NoteID = @.NoteID|||whats wrong with using nvarchar, how would I use a bit, forgive my ignorance!!|||A bit field is meant for true-false values. It can hold only a 1 or 0. It's just a more efficient way of storing a boolean value.|||But it is also more difficult to make use of data stored that way, plus if you have only 1 bit column it will still take a whole byte to store the data.

We typically use tinyint for this sort of data. This also prepares us for when the client suddenly decides that they need to store "Maybe" in addition to "Yes" and "No".

Just my 2 cents.

Terri|||I though tinyInt would have only allowed integers as opposed to "Yes", "No" "Maybe". Am I wrong?|||You are definitely correct. I use a separate lookup table to convert the integer value to its friendly name.

Terri|||Does that approach have significant advantages? In other words does it really make a considerable differance to speed etc using lookup tables like you have done or is that just a design preference?|||To be honest, we've used a mix of lookup tables and hardcoding in the front end. It has to be faster not to use lookup tables, but it's cleaner and takes up less space to just use a tinyint.

Also, you have prompted me to review the reasons we decided to use tinyints instead of bits. In case anyone is interested:

We found that different front ends would treat the bit data differently. VB6 in particular, I think, had some issues trying to convert a Boolean variable into a bit type. ADO did not translate bit into boolean -- at least not correctly or intuitively.

Issues I have seen with SQL Server:
-- MAX() will not accept a bit value, need to CAST the bit field as int or tinyint

And, as I mentioned, if the database design is changed from "yes/no" to "yes/no/maybe" the datatype would need to change and there would be related code changes.

Terri|||Also, lookup tables provide a consistent place to find your answers...you don't have to go tracking through your code to try and remember where you documented what your #'s represented. It pays of when you come back to your code several months later.

I agree with terri 100% on this one.|||Well...

There is an advantage to using the BIT column. For example, if you know that there must be a value, and you know that it is a true/false response, then why not use a bit? You can always allow a NULL value for 'maybe' cases, if necessary.

Personally, I only use bit when there is real true/false data to be stored, but I certainly wouldn't use a bit for such circumstances. For example, an IsActive field on a company record; why would you want a maybe as your response? If we send out a mailing to all active customers, maybe isn't going to help us at all. Either they are active, or they are not active. If we're not sure, we need to decide whtether we treat them as active or inactive (i.e., assign a default value).|||What if you decide to impliment more business logic...what if you decide that you want subscribers to go into a holding tank where they must be approved...before they can be on the mailing list. Then you would need a value to indicate that the are pending approval.

or maybe you have a delay in the system. Where you mark someone to be taken off the list..but they have to wait till the end of the month to be taken off the list. (like when you call people who are sending you junk mail and they tell you it will take 2 months till you stop getting mail...because they love sending you crap)

The point is...you never know when something will come up...why cut your legs off and use a bit, when an int functions just as good...and leaves you with more options in the future?|||Don't get me wrong here. There are many times when I will use a lookup table. However, if your analysis is solid, you should be able to discern these from actual true/false circumstances that arise in any database structures. And I think that such circumstances are best served using a bit value.