CODEWITHSUNDEEP
sqlsql-server
sansid
sansid

Reputation: 585

Bit field in where clause

I have a situation where I want to search for a field in the where clause only if the bit variable is 1 else ignore it.

@Active bit = 0

select * from Foo where firstname = 'xyz' and 

if(@active=1)

then search on the Active column else ignore the filtering on the Active column. How can I have that in a simple condition instead of checking each parameter seperately and then building the where clause

Upvotes: 2

Views: 6362

Answers (4)

Nitu Bansal
Nitu Bansal

Reputation: 3856

you can write this as below

 select * from Foo where firstname = 'xyz' and (@isactive=0 or (some conditions))

Upvotes: 0

blaklaybul
blaklaybul

Reputation: 850

How about:

DECLARE @Active bit

if @Active = 1
BEGIN
    (select * from Foo where firstname = 'bar' and Active = --condition)
END
else
BEGIN
    (select * from Foo where firstname = 'bar') 
END

of course, something will have to set the value for @Active somewhere between the declaration and the if...else statement.

Upvotes: 0

Anup Shah
Anup Shah

Reputation: 1254

it seems like Active is the Actual Column as well in your table. using Case stmt you can make the search efficient as it will use appropriate indexes you may have on this table.

    DECLARE @Active BIT=0
    SELECT * 
    FROM Foo 
    WHERE firstname = 'a' 
    AND active = CASE WHEN @Active=1 THEN @Active ELSE Active END

Upvotes: 0

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239646

Just simple logic will usually suffice:

select * from Foo where firstname = 'xyz' and
    (@Active = 0 or Active = <filter condition>)

For general advice about writing code for arbitrary search conditions, you could do worse than read Erland Sommarskog's Dynamic Search Conditions in T-SQL

Upvotes: 6

Related Questions