Geek asks and answers

SQL Server: where is the clause inside IF .. ELSE

As the name has already been mentioned, I am struggling with my proposal WHEREin the SQL stored procedure.

I received a query SELECTthat connects well to several tables and at the end, if I received a sentence WHEREthat gives specific values ​​for the search.

My problem is that I want to deploy this stored procedure for 2 different sentences WHERE, but I cannot get the right one IF ELSEto parse the request.

For instance:

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 @tempvarshould contain a comma separated identifier, like 10001,10002,10003)

I am struggling with the syntax and have been looking for some time, but cannot find the right solution.

Thanks in advance!

+3
3

CASE:

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

WHERE OR:

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

where:

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

TEMPVAR NULL, :

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

Another way to do this:

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 ....
0
source

More articles:


All Articles