SQL Server Cursor data as result of stored procedure

Question

Hello,

I have a stored procedure

DECLARE cursor FOR SELECT [FooData] From [FooTable];
OPEN cursor ;  
FETCH NEXT FROM cursor INTO @CurrFooData;
WHILE @@FETCH_STATUS = 0
BEGIN
  SELECT @CurrFooData AS FooData;
  INSERT INTO Bar (BarData) VALUES(@CurrFooData);
  FETCH NEXT FROM cursor INTO @CurrFooData;
END;
CLOSE cursor 
DEALLOCATE cursor 

But in result I have a lot of tables, not one. How can I return one table with 'FooData' column and all '@CurrFooData' rows?

Answer 1

Dmitry, I think you should really try to get rid of that cursor all together. In the second example, you're selecting two fields FooData1 and FooData2, but in the end, you're only ever inserting of the values....

You could rewrite that second query easily - something like:

INSERT INTO Bar (BarData) 
    SELECT FooData1 FROM FooTable

That's all that your entire cursor and everything is really doing.

Try to avoid cursors at all costs - 99% of the time, they're not needed - start to think in SETS of data - do not stick to procedural loops and cursor in SQL - that's just not a good match!

If you need to output what you've just inserted, use the OUTPUT clause:

INSERT INTO Bar (BarData) 
    OUTPUT Inserted.* 
    SELECT FooData1 FROM FooTable

Answer 2

Is it that you want to output the data you just inserted? If that is the case, and if you are using a version of SQL Server prior to SQL Server 2005, then you can stuff the values you want into a temp table like so:

Create Table #FooTemp ( FooData ... )

Insert #FooTemp( FooData )
Select FooData
From FooTable

Insert Bar( BarData )
Select FooData
From #FooTemp

Select FooData
From #FooTemp

The downside to this approach is that it will likely cause a recompile on your stored procedure each time it is run because of the temp table.

If you are using SQL Server 2000+ you could do the same as above only in a table variable:

Declare @FooTemp Table ( FooData ... )

Insert @FooTemp( FooData )
Select FooData
From FooTable

Insert Bar( BarData )
Select FooData
From @FooTemp

Select FooData
From @FooTemp

If you are using SQL Server 2005+, you can use the OUTPUT clause like so:

Insert Bar( BarData )
 OUTPUT inserted.BarData
Select FooData
From FooTable