IN（）子句中的子查询导致错误

I'm on SQL Server 2005 and I am getting an error which I am pretty sure should not be getting.

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

I am following the example# B on this MSDN link.

我正在关注此MSDN链接上的示例#B。

My stored proc code is as follows. I can simplify it for the sake of this post if you request so:

我存储的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

I have checked there is no datatype mismatch between the resultset from the subquery and the strSearchTypeCode the subquery result is compared with.

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

I see no reason why this should not work. If you have any clues then please let me know.

我认为没有理由不这样做。如果您有任何线索,请告诉我。

3 个解决方案

#1

Try rearranging the query so that the boolean expression occurs inside the subselect, e.g.

尝试重新排列查询,以便布尔表达式出现在子选择内部,例如

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

#2

I don't know that you can use the CASE statement inside of an IN clause like that. I'd suggest rewriting that bit to:

我不知道你可以像这样使用IN子句中的CASE语句。我建议重写一下:

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

or, if you really like the style you got there:

或者,如果你真的喜欢你在那里的风格:

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

In general, SQL should be smart to smart enough to optimize away the table if @bitQuickSearch = 1. But, I'd check the query plan just to be sure (trust, but verify).

一般来说,如果@bitQuickSearch = 1,SQL应该足够智能,以便优化掉表。但是,我只是确定查询计划(信任,但验证)。

#3

It seems to me that this SELECT:

在我看来,这个SELECT:

SELECT strSearchTypeCode FROM tblAdvanceSearchTypes

returns multiple rows, and that is your problem. You can rewrite it to be:

返回多行,这是你的问题。您可以将其重写为:

SELECT TOP 1 strSearchTypeCode FROM tblAdvanceSearchTypes

#1