469,111 Members | 1,964 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,111 developers. It's quick & easy.

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 getting 2 resultsets depending on conditon, In the second
conditon i am getting the above error could anyone help me..........
CREATE proc sp_count_AllNewsPapers
@CustomerId int
as
declare @NewsId int
set @NewsId = (select NewsDelId from NewsDelivery where
CustomerId=@CustomerId )
if not exists(select CustomerId from NewsDelivery where
NewsPapersId=@NewsId)

begin
select count( NewsPapersId) from NewsPapers
end
if exists(select CustomerId from NewsDelivery where
NewsPapersId=@NewsId)

begin
select count(NewsDelId) from NewsDelivery where
Customerid=@Customerid

end
GO
Jul 20 '05 #1
3 31107
On 20 Apr 2004 08:26:03 -0700, Nachi wrote:
I am getting 2 resultsets depending on conditon, In the second
conditon i am getting the above error could anyone help me..........
CREATE proc sp_count_AllNewsPapers
@CustomerId int
as
declare @NewsId int
set @NewsId = (select NewsDelId from NewsDelivery where
CustomerId=@CustomerId )
if not exists(select CustomerId from NewsDelivery where
NewsPapersId=@NewsId)

begin
select count( NewsPapersId) from NewsPapers
end
if exists(select CustomerId from NewsDelivery where
NewsPapersId=@NewsId)

begin
select count(NewsDelId) from NewsDelivery where
Customerid=@Customerid

end
GO


This is the cause of your problem:

set @NewsId = (select NewsDelId from NewsDelivery where
CustomerId=@CustomerId )

There are apparently at least two rows in NewsDelivery that satisfy
the condition CustomerId = @CustomerID. The value for @NewsID can only
be taken from one of these rows, but which one? You didn't tell SQL
Server how to choose, so SQL Server throws an error message.

Apart from this, I fail to see the logic of what you're doing, but I
think it can be done simpler. Can you explain what you try to achieve?

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)
Jul 20 '05 #2
Hugo Kornelis <hugo@pe_NO_rFact.in_SPAM_fo> wrote in message news:<a4********************************@4ax.com>. ..
On 20 Apr 2004 08:26:03 -0700, Nachi wrote:
I am getting 2 resultsets depending on conditon, In the second
conditon i am getting the above error could anyone help me..........
CREATE proc sp_count_AllNewsPapers
@CustomerId int
as
declare @NewsId int
set @NewsId = (select NewsDelId from NewsDelivery where
CustomerId=@CustomerId )
if not exists(select CustomerId from NewsDelivery where
NewsPapersId=@NewsId)

begin
select count( NewsPapersId) from NewsPapers
end
if exists(select CustomerId from NewsDelivery where
NewsPapersId=@NewsId)

begin
select count(NewsDelId) from NewsDelivery where
Customerid=@Customerid

end
GO


This is the cause of your problem:

set @NewsId = (select NewsDelId from NewsDelivery where
CustomerId=@CustomerId )

There are apparently at least two rows in NewsDelivery that satisfy
the condition CustomerId = @CustomerID. The value for @NewsID can only
be taken from one of these rows, but which one? You didn't tell SQL
Server how to choose, so SQL Server throws an error message.

Apart from this, I fail to see the logic of what you're doing, but I
think it can be done simpler. Can you explain what you try to achieve?

Best, Hugo


Hi Guys,

Thanks for kind Help....

Infact i have solved the problem by rewriting the procedure

CREATE proc sp_count_AllNewsPapers
@CustomerId int

as
declare @flag int
if exists (select CustomerId from NewsDelivery where CustomerId=@CustomerId )
begin
set @flag=0

end
else
begin
set @flag=1
end

if @flag=1
begin
select count( NewsPapersId) from NewsPapers
end

if @flag=0

begin
select count(NewsDelId) from NewsDelivery where Customerid=@Customerid

end
GO
Jul 20 '05 #3
On 21 Apr 2004 01:06:23 -0700, Nachi wrote:
Infact i have solved the problem by rewriting the procedure

(snip)

Hi Nachi,

Why make it more complex than necessary? The code in David Portas'
first message in this discussion is completely equivalent to your
code, but a lot shorter and easier to maintain.

(David's second suggestion will work as well, but uses some tricks
that not all SQL programmers will be able to understand - this might
make future maintenance more costly)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)
Jul 20 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by cincyn12 | last post: by
2 posts views Thread by reap76 | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by kglaser89 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.