CODEWITHSUNDEEP
sql-serverconditional-statementswhere-clause
nLL
nLL

Reputation: 5672

sql server conditional where

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.

Upvotes: 1

Views: 530

Answers (5)

IordanTanev
IordanTanev

Reputation: 6250

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 )

Upvotes: 2

Martin Smith
Martin Smith

Reputation: 453910

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.

Upvotes: 3

gbn
gbn

Reputation: 432662

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..

Upvotes: 1

Matt Gibson
Matt Gibson

Reputation: 38238

Simple.

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

Upvotes: 4

MaxouMask
MaxouMask

Reputation: 1037

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.

Upvotes: -2

Related Questions