bit datatype in where condition

Question

I have column "Status" of bit type in a table and in the declaration of parameters I have defaulted it to 0 and I have few other parameters coming in with defaulted to null

 @FirstName varchar(20) = null,
 @LastName varchar(20) = null,
 @Status bit = 0

and my sql is something like

 Select * from customers where 
 (ISNULL(@FirstName,'') =''  OR  FirstName= @FirstName)
 AND (ISNULL(@LastName,'') =''  OR  LastName= @LastName)
 AND (Status = @Status)

The situation is if only @FirstName value is sent from the code and the Value of the column "Status = 1" in the table and no value for @Status is sent, then since it @Status defaults to 0 no records get returned. How to deal with the bit type, in a situation where the parameter for the bit type is not sent and the value for it in the table is 1.

I'm not clear on what you're asking, and it doesn't help that your example code is unbalanced in terms of () bracketing. — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Oct 18, 2013 at 13:20
@Damien_The_Unbeliever added the proper brackets, thanks for pointing it out — sansid
– sansid, Commented Oct 18, 2013 at 13:31

podiluska · Accepted Answer · 2013-10-18 13:22:18Z

1

You can set the default value of your @status parameter to null, as you have with your other parameters.

@Status bit = null

You can rationalise your filter by doing this

 where
     isnull(@firstname, Firstname) = FirstName
 and
     isnull(@LastName, LastName) = LastName
 and 
     isnull(@Status, Status) = Status

Both this, and your original query, won't return rows where the value in the database is null.

answered Oct 18, 2013 at 13:22

podiluska

51.6k7 gold badges102 silver badges105 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

sansid Over a year ago

Even if I set it to null, the bit value defaults to false and will bring in a 0 from the code.

podiluska Over a year ago

There's either a problem with how you're calling the procedure, or how your parameters are declared.

Collectives™ on Stack Overflow

bit datatype in where condition

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related