SQL Server : Where clause inside IF .. ELSE

sql, sql-server, stored-procedures

Solution

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

Problem

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!

Original source

Related problems