sql server conditional where

Question

select * from table where category=@categoryid

I'm not sure how easy is this but I couldn't get my head around it. I want to be able to change where clause on above query so that if use 0 instead of 1-2-3 or 4 as @categoryid it would select all categories. i don't have any category with 0 in database.

Answer 1

hi,
you can set it to NULL when you want to select all categories an just modify select like this

select * from table where category= ISNULL( @categoryid, category )

Best Regards,
Iordan

Answer 2

This is SQL not PL/SQL. You need to test the value before sending the request you can not ask to SQL test it for you.

Answer 3

This should probably be divided into 2 separate queries unless you actually want the same execution plan of a full clustered index scan to be used both in the case that @categoryid=0 and @categoryid<>0

By dividing into 2 separate queries you will potentially allow the ones where @categoryid is not zero to be satisfied by an index seek rather than the full scan.

If the table is small or @categoryid is not very selective it might not be an issue however.

Answer 4

Simple.

select * from table where (category=@categoryid) OR (@categoryid = 0)

Answer 5

select * from table where 
category BETWEEN @mincategoryid AND @maxcategoryid

Min and max will one of

• both be 1 (or 2 or 3 or 4)
• respectively 0 and a high number

This will use an index too..