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 ?

Apparently that `select isbn` returns more than one value. You could use `where isbn IN (select isbn ...` — Hans Keﬆing, Apr 17 '13 at 07:22
Use `select top 1 isbn ...` or `select max(isbn) ...`. Even if you know only one row will be returned, sql-server doesn't know, because the error message is based on a statical analysis of the command, but not based on the real data. — Olivier Jacot-Descombes, Jun 10 '21 at 12:44

score 33 · Accepted Answer · answered Apr 17 '13 at 07:21

33

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

answered Apr 17 '13 at 07:21

Dan Puzey

33,626
4
73
96

I got that my friend,Thankz. I want to know one thing about this , only one is in books table refer to selected isbn , i want to select isbn from lending table,lend_no from lending table sudent_name from student table refer to index number in the lending table , how to retrive multiple rows from multiple tables?? – Roshan Apr 17 '13 at 07:41
2

No, the schema from the original question has `lended_date`. Actual defined schema beats good grammar every time, no matter how pedantic you want to be. – Dan Puzey Apr 23 '15 at 13:58
This is really the most concise and understandable description of IN that I have seen, thanks! – MDGREEE Feb 26 '22 at 04:05

praveen · Answer 2 · 2013-04-17T07:30:03.990

11

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

edited Apr 17 '13 at 07:30

answered Apr 17 '13 at 07:21

praveen

12,083
1
41
49

SET atRate1 = (SELECT t1.*,t2.* FROM RateCode t1 WHERE EXISTS (SELECT * FROM RateAmount t2 WHERE t1.RateCode = t2.RateCode AND t1.CountryCode = @CCode AND t1.ModuleCode = @MCode)) i am getting error on t2.* – Ashish-BeJovial Oct 20 '16 at 14:24

score 4 · Answer 3 · answered Apr 17 '13 at 07:20

4

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)

answered Apr 17 '13 at 07:20

bvr

4,786
1
20
24

score 0 · Answer 4 · answered Jun 10 '21 at 12:29

Using operator 'IN' helps

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 IN (select isbn from dbo.lending where (act between @fdate and @tdate) and (stat ='close'))

else if(@key='l')

    select * 
    from dbo.books 
    where isbn IN (select isbn from dbo.lending where lended_date between @fdate and @tdate)

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

4 Answers4

Linked