sql - 带 IN 语法的条件 WHERE 子句

Question

我需要编写带有条件 WHERE 子句的 SQL 查询。我的整个查询如下所示:

SELECT XXX
FROM table1 TBL 
JOIN table2 TBL2 ON TBL.Id = TBL2.TBL_Id 
WHERE TBL.Id = IIF(@variable IS NULL, Id, @variable )
AND TBL.Id IN( IIF(@tmp = 1, (SELECT Id FROM @tmpTable), Id))

我想达到的是那个

if @tmp is 1 then select all ids from @tmpTable, otherwise select all (equivalent to deletion of whole row)

以下条件有效(没有 IN 部分)

TBL.Id = IIF(@variable IS NULL, Id, @variable)

但是与 IN 部分相同的东西不能正常工作

TBL.Id IN( IIF(@tmp = 1, (SELECT Id FROM @tmpTable), Id))

它总是写错误:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

任何想法，我做错了什么？谢谢

Answer 1

我会做这样的事情；

SELECT XXX
FROM table1 TBL
JOIN table2 TBL2 
    ON TBL.Id = TBL2.TBL_Id
WHERE (@variable IS NULL AND TBL.Id = Id)
    OR (@tmp = 1 AND TBL.Id IN (SELECT Id FROM @tmpTable))