Subquery in an IN() clause causing error

Question

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

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.

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

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.

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

score 4 · Accepted Answer · answered Oct 02 '08 at 16:43

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

I'd make that a UNION ALL, as even if you have dupes, it doesn't really matter in an IN clause. — Mark Brackett, Oct 02 '08 at 16:47

score 2 · Answer 2 · answered Oct 02 '08 at 16:42

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:

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

score 2 · Answer 3 · answered Oct 02 '08 at 16:42

2

It seems to me that this 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

answered Oct 02 '08 at 16:42

Milan Babuškov

59,775
49
126
179

Subquery in an IN() clause causing error

3 Answers3