SQL Server: Where子句其他的

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

就像前面介绍的标题一样，我在SQL存储过程中遇到了WHERE子句。

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.

我得到了一个SELECT查询，它很好地连接了多个表，最后如果得到了WHERE子句，它给出了要搜索的特定值。

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

我的问题是，我想将这个存储过程扩展为两个不同的WHERE子句，但是我的IF ELSE不能正确解析查询。

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)

(@tempvar应该包含逗号分隔的id，比如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!

提前谢谢!

3 个解决方案

#1

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子句中与or连用:

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

#2

You can do this with directly logic in the where:

你可以在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:

如果TEMPVAR可以为空，则:

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)

#3

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

#1