Freelancer
Freelancer

Reputation: 9074

Handle more than one returning value in stored procedure

I have following stored procedure

ALTER PROC spInParam
(
    @partyCode NVARCHAR(50)
)
AS
BEGIN
DECLARE @totalAmount float
DECLARE @setLoop int
DECLARE @setCNT int
print 'Party Code '+@partyCode;
set @totalAmount=(select TotalAmount from BillParticular where partyCode=@partyCode)
set @setLoop=(select count(TotalAmount) from BillParticular where partyCode=@partyCode)
SET @setCNT=0;
WHILE @setCNT<=@setLoop
BEGIN
print 'Total Bill Amt.'+CONVERT(nvarchar(50),@totalAmount);
set @setCNT=@setCNT+1;
END
return CONVERT(NVARCHAR(50),@totalAmount)
END

In this stored procedure,

select TotalAmount from BillParticular where partyCode=@partyCode this query returns more than one values. (i.e. there are two TotalAmounts for particular @partyCode) how can i take them in loop?

I set while loop for this as shown in code.

This Stored Procedure compiled well. But while executing, It gave me following error:

Party Code 0L036
Msg 512, Level 16, State 1, Procedure spInParam, Line 11
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Please guid me what should be done in existing procedure.

Please tell me if i am making mistake anywhere.

Please help me.

Sample Output:

Party Code: 0L036
Total Bill Amount:1074432000
Total Bill Amount:2108208000

BillParticular table:

enter image description here

Edit:

DECLARE @partyCode nvarchar(50);
WITH records
AS
(
    SELECT  'Total Bill Amount : ' + CONVERT(nvarchar(50), TotalAmount) str_output
    FROM    BillParticular 
    WHERE   PartyCode = @partyCode
)
SELECT  'Party Code ' + @partyCode str_output
UNION ALL
SELECT  str_output FROM records

Returning NULL

Upvotes: 1

Views: 1206

Answers (1)

John Woo
John Woo

Reputation: 263723

WITH records
AS
(
    SELECT  'Total Bill Amount : ' + CONVERT(nvarchar(50), TotalAmount) str_output
    FROM    BillParticular 
    WHERE   PartyCode = @partyCode
)
SELECT  'Party Code ' + @partyCode str_output
UNION ALL
SELECT  str_output FROM records

Upvotes: 1

Related Questions