CODEWITHSUNDEEP
sql-servert-sql
AComputer
AComputer

Reputation: 529

SQL SERVER Subquery Error

Once I run this query, I get the following error.

DECLARE@Type int,@SearchStr2 nvarchar(200)
SET @Type=1
SET @SearchStr2 = 'A'
SELECT * FROM Document WHERE DocNo in(
CASE @Type
         WHEN 1 THEN 

         (SELECT DocNO FROM Publisher   WHERE CONTAINS((PublisherName),  @SearchStr2)
          )
         WHEN 2 THEN 
         (SELECT DocNO FROM Publisher   WHERE CONTAINS((PublishedPlace),  @SearchStr2)
          )
         WHEN 3 THEN 
         (SELECT DocNO FROM Publisher   WHERE CONTAINS((PublishedDate),  @SearchStr2)
          )

      END
      )

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

Upvotes: 1

Views: 154

Answers (2)

ta.speot.is
ta.speot.is

Reputation: 27214

CASE @Type WHEN ... END needs to evaluate to one value. Your subqueries aren't doing that, as per the error message.

You probably want to write something like this:

WHERE ( @Type = 1 AND DocNo IN ( SELECT DocNo FROM Publish WHERE <Use PublisherName> ) )
   OR ( @Type = 2 AND DocNo IN ( SELECT DocNo FROM Publish WHERE <Use PublishedPlace> ) )
   OR ...

Upvotes: 7

Anup Agrawal
Anup Agrawal

Reputation: 6669

One or more of these queries are returning more than 1 row.

SELECT DocNO FROM Publisher   WHERE CONTAINS((PublisherName),  @SearchStr2)

SELECT DocNO FROM Publisher   WHERE CONTAINS((PublishedPlace),  @SearchStr2)

SELECT DocNO FROM Publisher   WHERE CONTAINS((PublishedDate),  @SearchStr2)

Upvotes: 3

Related Questions