在子句中的 SQL 大小写

Question

查询有什么问题？

select * 
from ERP.dbo.Table 
where storeID in (case 
when @Designation = 80 
then 
(select storeID 
from ERP.dbo.Store 
where companyID = @CompanyID 
and isMonitoringAvailable = 1  
and storeID = ISNULL(@StoreId, storeID)) 
else 
(select storeID 
from ERP.dbo.EmployeeRole 
where employeeID = @EmployeeId 
and storeID = ISNULL(@StoreId,storeID) )  
end)

我收到此错误：

消息 512，级别 16，状态 1，第 46 行
子查询返回了 1 个以上的值。当子查询时不允许这样做 遵循 =、！=、<、<= 、>、>= 或将子查询用作表达式时。

Answer 1

您可以在下面重新编写您的条件

select * from ERP.dbo.EmployeeRole where
( storeID in (          
(select storeID 
from ERP.dbo.Store 
where companyID=@CompanyID and isMonitoringAvailable=1  
and storeID=ISNULL(@StoreId,storeID)
) and @Designation=80
) Or
storeID  in (Select storeID 
from ERP.dbo.EmployeeRole 
where employeeID=@EmployeeId 
and storeID=ISNULL(@StoreId,storeID)
)

您的查询返回错误，因为然后和否则结果生成多个层ID

Answer 2

CASE需要表达式，因此会出现错误。性能方面的替代方法是使用如下所示的UNION ALL运算符：

select * from ERP.dbo.EmployeeRole 
where @Designation=80 AND storeID in (  
select storeID 
from ERP.dbo.Store 
where companyID=@CompanyID and isMonitoringAvailable=1  
and storeID=ISNULL(@StoreId,storeID)
)
UNION ALL
select * from ERP.dbo.EmployeeRole 
where @Designation<>80 AND storeID in (  
select storeID 
from ERP.dbo.EmployeeRole 
where employeeID=@EmployeeId 
and storeID=ISNULL(@StoreId,storeID)
)

另一种方法是使用IF-ELSE，但UNION ALL会更好。