Subquery returned more than 1 value This is not permitted when the subquery follows or when the subquery is used as an expression

Question

I have a stored procedure that select   from book table   using sub query my query is   USE  library  GO          Object   StoredProcedure  dbo   report r and l     Script Date  04 17 2013 12 42 39          SET ANSI NULLS ON GO  SET QUOTED IDENTIFIER ON GO  ALTER procedure  dbo   report r and l   fdate date   tdate date   key varchar 1  as  if  key  r        select        from dbo books      where isbn   select isbn from dbo lending where  act between  fdate and  tdate  and  stat   close     else if  key  l        select        from dbo books      where isbn   select isbn from dbo lending where lended date between  fdate and  tdate    I know sub query is return more than one query to main query   But i don t know how to avoid this error  can any one help me

User · Accepted Answer

The problem is that these two queries are each returning more than one row:

select isbn from dbo.lending where (act between @fdate and @tdate) and (stat ='close')
select isbn from dbo.lending where lended_date between @fdate and @tdate

You have two choices, depending on your desired outcome. You can either replace the above queries with something that's guaranteed to return a single row (for example, by using SELECT TOP 1), OR you can switch your = to IN and return multiple rows, like this:

select * from dbo.books where isbn IN (select isbn from dbo.lending where (act between @fdate and @tdate) and (stat ='close'))

User · Answer

You can use IN operator as below  select   from dbo books where isbn IN  select isbn from dbo lending where lended date between  fdate and  tdate

User · Answer

Use In instead of     select   from dbo books  where isbn in  select isbn from dbo lending                  where act between  fdate and  tdate                 and stat   close                     or you can use Exists  SELECT t1   t2   FROM  books   t1  WHERE  EXISTS   SELECT   FROM dbo lending t2 WHERE t1 isbn   t2 isbn and                 t2 act between  fdate and  tdate and t2 stat   close

[sql-server] Subquery returned more than 1 value.This is not permitted when the subquery follows =,!=,<,<=,>,>= or when the subquery is used as an expression

Examples related to sql-server

Examples related to subquery