Returning multiple results from a Cursor into a table

The problem I'm having is that I need a cursor to return multiple results into 1 .NET System.Data.DataTable, and I don't know if it's possible. Actually, the real problem I'm having is that whoever designed this database did a poor job and if it weren't for one small thing I could do this all with aggregates...

But more specifically, this is what I need to accomplish. I have written a cursor that I know works - when I run it in MS SQL Query Analyzer, I get the results I want, but it's returned in multiple tables. I am maintaining a .NET application with some pretty lousy performance and I'm trying to boost it up. I need to take the cursor's results, stuff them into a DataTable, and then return that table to my web application to render.

In my C# code, I am using a SqlDataAdapter to execute my query. This is what I'd like to do...

/*assume query is my working cursor, InvoiceDBConnection is not null, and dt is an empty, non-null DataTable*/

SqlDataAdapter sda = new SqlDataAdapter(query, InvoiceDBConnection);
sda.Fill(dt);

... but this doesn't work, and it makes sense, I guess...since the cursor returns multiple "tables" (not sure if that's actually how the data is returned).

So is there any way to accomplish what I want to do? Or do I have to do it the crappy way it's done now by running my aggregate query and then using a really time-consuming for loop on my results to make the necessary adjustment?

Any help is greatly appreciated

Have you tried putting the results into a single temp table and just returning that the end of the cursor?

If you post your DDL along with your existing T-SQL then we may be able to shed some more light.

HTH!

|||No, I haven't tried that. I'm pretty new to cursors, so I didn't know I could do that. Here's my cursor so far...

Code Snippet

DECLARE @.@.invoiceID int
DECLARE @.@.additions decimal
DECLARE @.@.subtractions decimal
DECLARE @.@.adjustments decimal
DECLARE @.@.payments decimal

DECLARE invoiceCursor CURSOR SCROLL
FOR
SELECT Invoice_ID FROM Invoice WHERE Customer_ID = 115
OPEN invoiceCursor
FETCH FIRST FROM invoiceCursor INTO @.@.invoiceID
WHILE @.@.FETCH_STATUS <> -1
BEGIN

SELECT @.@.additions = SUM(Amount)
FROM [Adjustment]
WHERE Is_Addition = 1 AND Invoice_ID = @.@.invoiceID;
SELECT @.@.subtractions = SUM(Amount)
FROM [Adjustment]
WHERE Is_Addition = 0 AND Invoice_ID = @.@.invoiceID;
SELECT @.@.adjustments = (CASE WHEN @.@.additions IS NULL THEN 0 ELSE @.@.additions END) - (CASE WHEN @.@.subtractions IS NULL THEN 0 ELSE @.@.subtractions END);
SELECT @.@.payments = CASE WHEN SUM(Payment_Amount) IS NULL THEN 0 ELSE SUM(Payment_Amount) END FROM Payment WHERE Invoice_ID = @.@.invoiceID

SELECT i.*, CASE WHEN i.Paid_In_Full = 1 THEN '<font face=''Arial'' size=''1'' color=''green''>Yes</font>' ELSE '<font face=''Arial'' size=''1'' color=''red''>No</font>' END AS Paid, @.@.payments AS Amount_Paid,
(Invoice_Amount - @.@.payments + @.@.additions) AS Remaining, CASE WHEN DATEDIFF(dd, Terms_Start_Date, GETDATE()) < 0 THEN 0 WHEN i.Paid_In_Full = 1 THEN 0 ELSE DATEDIFF(dd, Terms_Start_Date,
GETDATE()) END AS Terms_Days_Passed
FROM [Invoice] i
WHERE Invoice_ID = @.@.invoiceID

FETCH NEXT FROM invoiceCursor INTO @.@.invoiceID
END
CLOSE invoiceCursor
DEALLOCATE invoiceCursor

So it sounds like I need to declare a table somehow, and stuff the highlighted select statement into that table, and then Return that table?

Do you know the syntax for that offhand, or maybe know of an article I could read to do it? Thanks!

|||

Sure, check out Books Online for table variables and temporary tables:

In pesudo code, you'd do something like:

Code Snippet

DECLARE @.AllYour variables

......

-- create your temp table

CREATE TABLE #InvoiceStuff

(iCol1 INT, iCol2 INT, strHTML VARCHAR(100))

OPEN CURSOR

INSERT INTO #InvoiceStuff
SELECT *
FROM Invoice
WHERE InvoiceID = @.InvoiceID

FETCH NEXT FROM Cursor

CLOSE Cursor

SELECT *
FROM #InvoiceStuff

DROP TABLE #InvoiceStuff

One other thing to mention, i'd suggest using single @.'s for your local variables as those with @.@. are system globals eg @.@.Fetch_Status and @.@.Version.

HTH!!

|||Awesome, thanks a lot. And I didn't know that about the local variables. I'm going to give this a shot.