Dynamic WHERE clause in a stored procedure

Question

Post reply

Dynamic WHERE clause in a stored procedure

ryan.hart

SSChasing Mays

Points: 602
More actions
January 13, 2009 at 2:08 pm

#94450

Hello All -
I'm having trouble figuring out the best way to handle a dynamic WHERE clause in a stored procedure. Below is what I'm trying to accomplish. The 3 commented out lines are what should be dynamic, based on the value of @Action. I tried a CASE, but had trouble getting it to work with IN (not =). For business reasons that don't apply to the procedure that I'm working on, I do need to be able to handle 9 values for @Action. 2 of them will actually change the results of the SELECT, the other 7 will just omit that filter, all returning the same results.
Thanks for the help!
DECLARE @Action char(1)
DECLARE @VendorID int
DECLARE @Count int
SET @Action = 'a'
SET @VendorID = 1
SELECT @Count = COUNT(DownloadID)
FROM dbo.tblDownload
WHERE VendorID = @VendorID
--AND DownloadStatusID IN (1,2) - @Action = a
--AND DownloadStatusID IN (3,4) - @Action = b
--omit this filter all together - @Action = c,d,e,f,g,h,i
AND DeltaDownloadID >
(
SELECT DeltaDownloadID
FROM tblDeltaDownloadTracking
WHERE VendorID = @VendorID
)

Viewing 4 posts - 1 through 3 (of 3 total)

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

null-512060 SSChasing Mays Points: 613 More actions · Answer 1

Please try:

SELECT @Count = COUNT(DownloadID)

FROM dbo.tblDownload

WHERE VendorID = @VendorID

AND (@Action NOT IN (‘a’,’b’)

OR (@Action = ‘a’ and DownloadStatusID IN (1,2))

OR (@Action = ‘b’ and DownloadStatusID IN (3,4)) )

AND DeltaDownloadID >

( SELECT DeltaDownloadID

FROM tblDeltaDownloadTracking

WHERE VendorID = @VendorID )

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 2

I don't have any data to test on, but be careful... that looks an awful lot like a triangular join to me. For more info on why that could be a bad thing, please see the following...

http://www.sqlservercentral.com/articles/T-SQL/61539/

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

ryan.hart SSChasing Mays Points: 602 More actions · Answer 3

@Old Hand -

Thanks very much! That's a great solution.

@jeff -

I'd read that article once a long time ago. Thanks for reminding me about the problems with triangular joins. I have tons of test data in my dev and QA environments and I'll be sure to test that thoroughly with lots of rows. I’ll also have another developer here look at it.

Thanks again to both of you.