If statement in WHERE clause of Stored Procedure

Question

Post reply

If statement in WHERE clause of Stored Procedure

hydera

SSC-Addicted

Points: 410
More actions
July 18, 2005 at 1:51 pm

#67739

I have have a parameter in a stored procedure which I wish to use in the WHERE clause of a SELECT statement as a filter, varCategory is an integer and can be null. Is anyone able to tell me if the pseudo code below is possible in T-SQL. ie is an IF or CASE statement permissable in a WHERE clause?
declare @varCategory int
Select * from tblparticipants
where
IF varCategory = NULL then
--ignore where clause
ELSE IF varCategory = 1 or 2 or 3 THEN
--filter on varCategory = @varCategory
END IF

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Roy Rogers SSCertifiable Points: 7712 More actions · Answer 1

declare @varCategory int

Select * from tblparticipants

Where

varCategory = CASE WHEN @varCategory IS NULL THEN varCategory ELSE @varCategory END

Regards,
gova

ND-215452 SSCrazy Points: 2017 More actions · Answer 2

I just thought the query maybe much better without using if-else, CASE etc..

select *

from tblparticipants

where varCategory is null

union

select *

from tblparticipants

where varCategory = @varCategory

kkraemer SSC Rookie Points: 38 More actions · Answer 3

Try this:

select *

from tblparticipants

where varCategory = isnull(@varCategory, varCategory)

If the parm is null, the value from the current row is used, which will in effect just ignore null

Roy Rogers SSCertifiable Points: 7712 More actions · Answer 4

Roy Rogers

SSCertifiable

Points: 7712

July 19, 2005 at 7:18 am

#575464

That is a good one.

Regards,
gova

childhood SSC Rookie Points: 33 More actions · Answer 5

The near same "hydera" but I have 2 variables varCategory, varName and I need condition AND beetween

varCategory AND varName below:

declare @varCategory int

declare @varName varchar(50)

Select * from tblparticipants

where

IF varCategory = NULL then

--ignore where clause

IF varCategory = 1 or 2 or 3 THEN

--varCategory = @varCategory

IF varName = NULL then

--ignore where clause

IF varName != NULL then

AND varName = @varName (I mean, where clause varCategory AND varName)

END IF

In above, how must we do it? Please help me.

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 6

childhood (10/31/2015)
The near same "hydera" but I have 2 variables varCategory, varName and I need condition AND beetween
varCategory AND varName below:
declare @varCategory int
declare @varName varchar(50)
Select * from tblparticipants
where
IF varCategory = NULL then
--ignore where clause
IF varCategory = 1 or 2 or 3 THEN
--varCategory = @varCategory
IF varName = NULL then
--ignore where clause
IF varName != NULL then
AND varName = @varName (I mean, where clause varCategory AND varName)
END IF
In above, how must we do it? Please help me.

Gosh. This whole thread has been about how to do it. At least try.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)