CODEWITHSUNDEEP
t-sql
David Adlington
David Adlington

Reputation: 666

Dynamic where Clause in SQL

I would like to be able to alter a where clause in a Stored Procedure based upon the value of a parameter passed to it.

E.G.

This is how I think it should work but I cannot get it quite right 

Declare @param as int

set @param = 1

Select Productname
from product
where
 case @param = 1 then productname = 1
 else productname <> 1
 end

I have been looking at Dynamic SQL etc?

Thanks in advance

Upvotes: 1

Views: 514

Answers (1)

Mark Byers
Mark Byers

Reputation: 838226

Try this WHERE clause:

WHERE (@param = 1 AND productname = 1)
OR    (@param <> 1 AND productname <> 1)

The parentheses are not strictly needed here because AND has higher precedence than OR but I've added them anyway for clarity.

Upvotes: 4

Related Questions