Can I add if clause in where clause

Question

In stored procedure I have:

@parameter int

based on this parameter I get some data it can be -1 or greater than 0.

Problem is that if it is -1 I don't want to this parameter be in where clause at all.

So what I am trying to do is something like:

IF @parameter = -1 THEN
WHERE ...
ELSE
where ... AND Column = @parameter

But this doesn't work.

little word of caution if you are aiming at "catch all queries" as they can perform badly if you don't take into account (and counter) execution plan caching: http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/ — buckley, May 17 '12 at 19:16

score 7 · Accepted Answer · answered May 17 '12 at 18:54

7

Typically you'd apply that constraint like this:

WHERE [...] AND (@parameter = -1 OR Column = @parameter)

answered May 17 '12 at 18:54

Dan J

score 1 · Answer 2 · edited May 17 '12 at 19:39

1

where 
  @parameter != - 1 AND Column = @parameter

The diff between Dan J and my answer is that nothing is matched if @parameter is -1. Dan J returns everything and is the behaviour you probably want.

edited May 17 '12 at 19:39

marc_s

answered May 17 '12 at 18:55

buckley

I like mine better (and `!=` isn't the SQL not-equal operator). ;) – Dan J May 17 '12 at 19:04
!= and <> work all the same in sql server no? What is the not-equal operator then? – buckley May 17 '12 at 19:04
Ah, you're right, SQL Server supports `!=`. I've always used `<>`, which I believe [is ANSI standard](http://stackoverflow.com/a/723317/238688). – Dan J May 17 '12 at 19:15

2 Answers2