Return 2 Recordsets from 1 Stored Proc
I am trying to run the following stored proc from asp.net 2.0 and have two record sets returned.
How do i go about doing this? Is it possible?
If I execute the SP on SqlServer it returns 2 recordsets as i want... Just unsure about asp.net. I realize I could use 2 stored procs to achieve the same thing but want to know how to do it with one.
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
ALTER PROCEDURE [dbo].[spgGetCreditorControl]
SET NOCOUNT ON;
SELECT PurchaseInvSummary.Date AS PurchaseDate,
PurchaseInvSummary.Reference AS PurchaseRef,
PurchaseInvSummary.Gross AS PurchaseGross,
FROM PurchaseInvSummary INNER JOIN
Customer ON (PurchaseInvSummary.CreditorID = Customer.ID)
WHERE (PurchaseInvSummary.CreditorID = @CreditorID) AND
(Date >= @StartDate) AND
(Date <= @EndDate)
SELECT [Transaction].Date AS TransactionDate,
[Transaction].Narrative AS TransactionNarrative,
[Transaction].Amount AS TransactionAmount
FROM [Transaction] INNER JOIN
AccountType ON [Transaction].AccountID = AccountType.AccountID
WHERE AccountType.CustomerID = @CreditorID AND
[Transaction].Date >= @StartDate AND
[Transaction].Date <= @EndDate