sql server-in()子句中的子查询导致错误

Question

我在SQL Server 2005上，遇到了一个错误，我确信不应该出现这个错误。

Msg 512, Level 16, State 1, Procedure spGetSavedSearchesByAdminUser, Line 8 Subquery
returned more than 1 value. This is not permitted when the subquery
follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

我正在遵循上的示例#B此MSDN链接。

我存储的proc代码如下。如果你要求的话，我可以为了这篇文章而简化它：

ALTER PROCEDURE [dbo].[spGetSavedSearchesByAdminUser] 
    @strUserName varchar(50)  
    ,@bitQuickSearch bit = 0
AS
BEGIN
    SELECT [intSearchID] ,strSearchTypeCode ,[strSearchName]
    FROM [tblAdminSearches] 
    WHERE 
        strUserName = @strUserName
        AND 
        strSearchTypeCode 
            IN (
                CASE @bitQuickSearch 
                WHEN 1 THEN 'Quick' 
                ELSE (SELECT strSearchTypeCode FROM tblAdvanceSearchTypes) 
                END
            )
    ORDER BY strSearchName
END

我已经检查了子查询的结果集和与子查询结果进行比较的strSearchTypeCode之间没有数据类型不匹配。

我看不出有什么理由不这样做。如果你有任何线索，请告诉我。

Answer 1

尝试重新排列查询，使布尔表达式出现在子选择中，例如

ALTER PROCEDURE [dbo].[spGetSavedSearchesByAdminUser] 
    @strUserName varchar(50)  
    ,@bitQuickSearch bit = 0
AS
BEGIN
    SELECT [intSearchID] ,strSearchTypeCode ,[strSearchName]
    FROM [tblAdminSearches] 
    WHERE 
        strUserName = @strUserName
        AND 
        strSearchTypeCode 
                IN (SELECT strSearchTypeCode FROM tblAdvanceSearchTypes where @bitQuickSearch=0
                    UNION
                    SELECT 'Quick' AS strSearchTypeCode WHERE @bitQuickSearch=1)
    ORDER BY strSearchName
END

Answer 2

我不知道你是否可以在这样的IN子句中使用CASE语句。我建议将这一点改写为：

WHERE strUserName = @strUserName AND (
   (@bitQuickSearch = 1 AND strSearchTypeCode = 'Quick')
   OR
   (strSearchTypeCode IN (SELECT strSearchTypeCode FROM tblAdvanceSearchTypes))
)

或者，如果你真的喜欢那里的风格：

WHERE strUserName = @strUserName 
   AND strSearchTypeCode IN (
      SELECT CASE @bitQuickSearch WHEN 1 THEN 'Quick' ELSE strSearchTypeCode END
      FROM tblAdvanceSearchTypes
   )

一般来说，如果@bitQuickSearch=1，SQL应该足够聪明，可以优化掉表。但是，我检查查询计划只是为了确定（信任，但要验证）。

Answer 3

在我看来，这个SELECT：

SELECT strSearchTypeCode FROM tblAdvanceSearchTypes

返回多行，这就是您的问题。您可以将其重写为：

SELECT TOP 1 strSearchTypeCode FROM tblAdvanceSearchTypes