Dynamic where Clause in SQL

Question

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

Mark Byers · Accepted Answer · 2012-07-10 15:39:45Z

4

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.

answered Jul 10, 2012 at 15:39

Mark Byers

844k202 gold badges1.6k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

David Adlington Over a year ago

This works, must be my brain this morning but I am struggling to see exactly why! Boolean Logic I am guessing, need to re-fresh my knowledge. Thanks Mark

Andriy M Over a year ago

@DavidA: This is logical equality for you, expressed through conjunction and disjunction: (a EQ b -> (a AND b) OR (NOT a AND NOT b)). The closest to the actual EQ operator I could get in Transact-SQL is this non-sargable expression: WHERE CASE productname WHEN 1 THEN 1 ELSE 0 END = CASE @param WHEN 1 THEN 1 ELSE 0 END.

Collectives™ on Stack Overflow

Dynamic where Clause in SQL

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related