Subquery in an IN() clause causing error

Question

hi, 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:

http://msdn.microsoft.com/en-us/library/ms177682(SQL.90).aspx

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.

Thanks in advance.

Aamir

Answer 1

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

Answer 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

Answer 3

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

Answer 4

Thanks all! may be the CASE statement is indeed not allowed in the IN() clause. I hope to see it there in the next version.

Thanks again.