Reputation: 9074
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:
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
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