SQL Server : Where clause inside IF .. ELSE

Question

Like the title already explained, I'm struggling with my WHERE clause in my SQL stored procedure.

I got a SELECT query which joins multiple tables well and at the end if got a WHERE clause that give specific values to search for.

My problem is that I want to expand this stored procedure for 2 different WHERE clauses, but I can't get my IF ELSE correct to parse the query..

For example:

SELECT ....... 
FROM TABLE_X
INNER JOIN TABLE_Y.....
WHERE 
    man.Klant_ID = @Klant
    AND (@ManID = 0 OR man.ID = @ManID)
    AND .... (which continues like the rule above)

Here I want to get something like this:

SELECT ....... 
FROM TABLE_X
INNER JOIN TABLE_Y.....

IF @TEMPVAR = ''
    WHERE man.Klant_ID=@Klant
    AND (@ManID = 0 OR man.ID = @ManID)
    AND... 
ELSE
    WHERE TABLE_X.ID IN (@TEMPVAR)

(and @tempvar should contain comma separated id's like 10001,10002,10003)

I'm struggling with the syntax and searched for some while but can't seem to find a right solution.

Thanks in advance!

this might help http://stackoverflow.com/questions/10191424/case-in-where-sql-server — Mark Giaconia, Feb 11 '14 at 21:42
Quite simply.... you cant do that... You need to put the entire select clause in your IF ELSE.. — logixologist, Feb 11 '14 at 21:43
When writing SQL, don't think of `IF/ELSE` control structures as mechanisms to _build_ SQL statements. Instead, any logic related to the results returned by the query should be built into its `WHERE` clause and the variables can be used there just as they can in scope of the function or stored procedure. — Michael Berkowski, Feb 11 '14 at 21:45
Try using a BEGIN and END statement in between your IF statements. http://technet.microsoft.com/en-us/library/ms182717.aspx — HKImpact, Feb 11 '14 at 21:47
I updated my question with more information.. My orignal where.. And logic was already like the first given comment here. — Yosoyke, Feb 11 '14 at 21:48

D Stanley · Accepted Answer · 2014-02-11T21:51:20.623

4

You can do that with a CASE:

SELECT ....... FROM TABLE_X
INNER JOIN TABLE_Y.....
WHERE TABLE_X.FIRSTCOLUMN = 
    CASE WHEN @TEMPVAR = '' THEN 123
         ELSE 456
    END

or directly in the WHERE clause with an OR:

WHERE (@TEMPVAR = ''
       AND man.Klant_ID=@Klant
       AND (@ManID = 0 OR man.ID = @ManID)
       AND... 
      ) 
      OR
      (@TEMPVAR <> '' 
       AND TABLE_X.ID IN (@TEMPVAR)
      )

edited Feb 11 '14 at 21:51

answered Feb 11 '14 at 21:43

D Stanley

149,601
11
178
240

This is indeed a correct solutions for a problem.. But I kinda explained my question wrong.. I've updated my question.. But I appreciate the effort! – Yosoyke Feb 11 '14 at 21:47
This does indeed separate the clauses perfectly like I wish, so I'm gonna accept your answer! Extra question: the @Tempvar I would like to contain the comma-seperated id's to use, for example: .. where ID IN (@TEMPVAR) (would be WHERE ID IN (10001,10002,10003)) .. How can I make this possible? Now I declared the TempVar to be 'nvarchar(max)' because it contains commas to.. – Yosoyke Feb 11 '14 at 22:23
That's not simple - you either need to use dynaimc SQL (building a string and appending the comma separated list) or build a function to split the string into a temp table and join to it. See [here](http://stackoverflow.com/questions/17481479/parse-comma-separated-string-to-make-in-list-of-strings-in-the-where-clause) – D Stanley Feb 11 '14 at 23:05
Ok, thx a lot for your time! Will look at this solutions to fix me IN-problem aswell! – Yosoyke Feb 12 '14 at 19:04

score 2 · Answer 2 · answered Feb 11 '14 at 21:45

You can do this with directly logic in the where:

SELECT ....... FROM TABLE_X
INNER JOIN TABLE_Y.....
WHERE TABLE_X.FIRSTCOLUMN = 123 and @TEMPVAR = '' or
      TABLE_X.FIRSTCOLUMN = 456 and @TEMPVAR <> ''

If TEMPVAR can be NULL, then:

SELECT ....... FROM TABLE_X
INNER JOIN TABLE_Y.....
WHERE TABLE_X.FIRSTCOLUMN = 123 and @TEMPVAR = '' or
      TABLE_X.FIRSTCOLUMN = 456 and (@TEMPVAR <> '' or @TEMPVAR is null)

score 0 · Answer 3 · answered Feb 11 '14 at 21:46

0

The other way to do it is:

IF @TEMPVAR = ''
SELECT ....... FROM TABLE_X
INNER JOIN TABLE_Y.....
WHERE TABLE_X.FIRSTCOLUMN = 123
    AND ....
ELSE
SELECT ....... FROM TABLE_X
INNER JOIN TABLE_Y.....
WHERE TABLE_X.FIRSTCOLUMN = 456
    AND ....

answered Feb 11 '14 at 21:46

logixologist

3,694
4
28
46

SQL Server : Where clause inside IF .. ELSE

3 Answers3