
October 4th, 2004, 09:06 AM
|
|
Contributing User
|
|
Join Date: Jun 2002
Location: Pennsylvania, USA
|
|
|
Returning more than one result set from a stored procedure
Hi, I am wondering wether it is possible to return more than one result set to my application from a stored procedure. My sp is below, but all that returns to my application is the result of the first iteration of the invoice_cursor. Any help or alternate methods to achieve this would be appreciated.
Code:
CREATE PROCEDURE procBillingSummaryCastingDollars(
@startDate Varchar(10),@endDate Varchar(10)) AS
DECLARE @invoice_id varchar(11);
DECLARE invoice_cursor CURSOR FOR
SELECT invoice_id
FROM receivable_dist rd
WHERE rd.POSTING_DATE BETWEEN @startDate AND @endDate
AND (rd.GL_ACCOUNT_ID like '%3000%' OR rd.GL_ACCOUNT_ID like '%3101%')
order by invoice_id
OPEN invoice_cursor
FETCH NEXT FROM invoice_cursor
INTO @invoice_id
WHILE @@FETCH_STATUS = 0
BEGIN
-- Declare an inner cursor based
-- on invoice_id from the outer cursor.
IF charindex('C',@invoice_id) = 0 AND charindex('M',@invoice_id) = 0
BEGIN
SELECT ss.metal_code, ss.equip_code, ss.core,
CASE r.status WHEN 'X' THEN rl.amount*-1 ELSE rl.amount END as CastingDollars
FROM receivable r
INNER JOIN receivable_line rl ON r.invoice_id = rl.invoice_id
INNER JOIN cust_order_line cl ON cl.cust_order_id = rl.cust_order_id
INNER JOIN salesum_partid ss ON ss.partid = cl.part_id
WHERE rl.invoice_id = @invoice_id
END
ELSE
BEGIN
SELECT ss.metal_code, ss.equip_code, ss.core,
CASE r.status WHEN 'X' THEN rl.amount*-1 ELSE rl.amount END as CastingDollars
FROM receivable r
INNER JOIN receivable_line rl ON r.invoice_id = rl.invoice_id
INNER JOIN salesum_partid ss
ON substring(ltrim(rl.reference),1,(patindex('%-_-____%',ltrim(rl.reference))+6)) = ss.partid
WHERE r.invoice_id = @invoice_id
END
FETCH NEXT FROM invoice_cursor
INTO @invoice_id
END
CLOSE invoice_cursor
DEALLOCATE invoice_cursor
GO
|