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

[Nachi]

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_AllNew sPapers
@CustomerId int
as
declare @NewsId int
set @NewsId = (select NewsDelId from NewsDelivery where
CustomerId=@Cus tomerId )
if not exists(select CustomerId from NewsDelivery where
NewsPapersId=@N ewsId)

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

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

end
GO

[Hugo Kornelis]

Re: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, &lt;, &lt;= , &gt;, &gt;= or when the subquery is used as an expression.

On 20 Apr 2004 08:26:03 -0700, Nachi wrote:
[color=blue]
>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_AllNew sPapers
>@CustomerId int
>as
>declare @NewsId int
>set @NewsId = (select NewsDelId from NewsDelivery where
>CustomerId=@Cu stomerId )
> 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=@Cu stomerid
>
> end
>GO[/color]

This is the cause of your problem:

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

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)

[Nachi]

Re: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, &lt;, &lt;= , &gt;, &gt;= or when the subquery is used as an expression.

Hugo Kornelis <hugo@pe_NO_rFa ct.in_SPAM_fo> wrote in message news:<a4ha801te 5sfae4ok1ebsh14 ojfk8fbmt8@4ax. com>...[color=blue]
> On 20 Apr 2004 08:26:03 -0700, Nachi wrote:
>[color=green]
> >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_AllNew sPapers
> >@CustomerId int
> >as
> >declare @NewsId int
> >set @NewsId = (select NewsDelId from NewsDelivery where
> >CustomerId=@Cu stomerId )
> > 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=@Cu stomerid
> >
> > end
> >GO[/color]
>
> This is the cause of your problem:
>
> set @NewsId = (select NewsDelId from NewsDelivery where
> CustomerId=@Cus tomerId )
>
> 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[/color]

Hi Guys,

Thanks for kind Help....

Infact i have solved the problem by rewriting the procedure

CREATE proc sp_count_AllNew sPapers
@CustomerId int

as
declare @flag int
if exists (select CustomerId from NewsDelivery where CustomerId=@Cus tomerId )
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=@Cus tomerid

end
GO

[Hugo Kornelis]

Re: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, &lt;, &lt;= , &gt;, &gt;= or when the subquery is used as an expression.

On 21 Apr 2004 01:06:23 -0700, Nachi wrote:
[color=blue]
>Infact i have solved the problem by rewriting the procedure[/color]
(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)