SQL Server Stored Procedure question

[JohnnyPanic]

I've got a Stored Procedure in MSSQL Server (2005 and 2008, sigh) that returns a recordset to a classic asp page (don't ask!).

Stripping out the declarations it looks a little something like this:

BEGIN
 SET NOCOUNT ON;

 SELECT Col1, Col2
 FROM MyTable
 WHERE Col1 BETWEEN @lower AND @upper
 ORDER BY Col2

END

This works fine and returns the correct recordset.

But what I want to do is iterate through each returned row within the stored procedure as well as return the recordset to the code. Basically, the procedure is bringing out a list of results and I want to save a record for each time an individual item is part of a list.

So, within a stored procedure is it possible to iterate through a recordset and also output it to the code that called the procedure?

[neilus]

In short 'Yes'.

You want to output the result set to a table to insure consistent results (I'm assuming that the table is changing so it the result set cannot be guaranted to be the same on each call). Assuming it is a small result set you can use a table variable, if it's larger you may want to consider a table variable.

 DECLARE @temp TABLE (col1 INT, col2 INT);
 INSERT INTO @temp (col1, col2) 
 SELECT Col1, Col2 FROM MyTable WHERE Col1 BETWEEN @lower AND @upper ORDER BY Col2; 

--You can then use a cursor (urgh!) to iterate through the record set.

DECLARE @col1   int,
        @col2   int;
DECLARE xcursor CURSOR FOR
SELECT col1, col2
FROM @temp;

OPEN xcursor;

FETCH NEXT FROM xcursor
INTO @col1, @col2;

WHILE @@FETCH_STATUS = 0
BEGIN
      --do what you want...

      FETCH NEXT FROM xcursor  
      INTO @col1, @col2;
END
CLOSE xcursor;
DEALLOCATE xcursor;

--And then finally return the record set to the calling ASP page...

SELECT col1, col2
FROM @temp;

Of course I have assumed both col1 and col2 are integers in your example.

Edited by neilus on Friday 3rd December 18:31

[JohnnyPanic]

Great, thanks Neil.

Could you just clarify what you meant here? I'm guessing that not both small & large would be variables?

neilus said:

Assuming it is a small result set you can use a table variable, if it's larger you may want to consider a table variable.

Cheers

[neilus]

JohnnyPanic said:

Great, thanks Neil.

Could you just clarify what you meant here? I'm guessing that not both small & large would be variables?

neilus said:

Assuming it is a small result set you can use a table variable, if it's larger you may want to consider a table variable.

Cheers

Sorry my bad, if it's a large result set you may want to use a temporary table instead of a table variable. In simplistic terms a table variable is held in memory like any other variable and a temporary table is held in TempDB.

Not sure of you skill set so if I'm teaching you to suck eggs apologies. A temporary table is created like any other table in SQL but has the prefix # (i.e. #temp) So, the table would be created as:

CREATE TABLE #temp (col1 INT, col2 INT);

You would then need to change the references from @temp to #temp.

[JohnnyPanic]

No, that's great, thanks Neil. It's not a core skill of mine, just picking it up as I go along to achieve what I want to do!

Thanks

[Crafty_]

Many people will tell you cursors are the work of the devil.

They aren't great for performance, alot of DBAs wont use them at all.

For the example above its a bit daft IMHO.. why use a cursor to loop through ? why not just return the data from the initial select ?

The OP mentioned inserting a record for each row that the query returns, why not
insert into ... (col1, col2) select colA, colB from ...

now you have a choice - if this is fast moving data you could run the query again and return the result, the problem here is you *might* get a row return by the proc that we didn't insert into the other table for, because this new row was inserted in between the operations. So to safeguard from this, run the query, inserting the results into a temp table, then do the "insert into (..) select .. from" using the temp table as the source, then select all records to return them.

Something like:

DECLARE @temp TABLE (col1 INT, col2 INT)
 INSERT INTO @temp (col1, col2) 
 SELECT Col1, Col2 FROM MyTable WHERE Col1 BETWEEN @lower AND @upper ORDER BY Col2; 

insert into othertable(col1, col2) select col1, col2 from @temp
select col1, col2 from @temp

Edited by Crafty_ on Saturday 4th December 23:07

[JohnnyPanic]

Interesting. Can I also return the number of each row in the temp table to 'othertable'?
e.g.

DECLARE @temp TABLE (col1 INT, col2 INT, RowID int IDENTITY)
    INSERT INTO @temp (col1, col2) 
    SELECT Col1, Col2 FROM MyTable WHERE Col1 BETWEEN @lower AND @upper ORDER BY Col2; 

insert into othertable(col1, col2, Position) select col1, col2, RowID from @temp
select col1, col2 from @temp

Sorry, SQL isn't my first language, or indeed second or third!

[JohnnyPanic]

Though from the looks of this http://support.microsoft.com/kb/186133 I might also be able to use rank() or row_count(). Are they efficient functions?

e.g.

DECLARE @temp TABLE (col1 INT, col2 INT, RowID int)
    INSERT INTO @temp (col1, col2, RowID) 
    SELECT Col1, Col2, row_number() over (ORDER BY Col2 ASC) as RowID 
    FROM MyTable WHERE Col1 BETWEEN @lower AND @upper ORDER BY Col2; 

insert into othertable(col1, col2, Position) select col1, col2, RowID from @temp
select col1, col2 from @temp

Apologies for using two posts, the PH parser doesn't seem to let you insert more than one code block.

Thanks guys

[Crafty_]

row_count should do the job, I don't think its a huge performance hit, better than using a cursor anyway

[FormulaCrew]

JohnnyPanic said:

Interesting. Can I also return the number of each row in the temp table to 'othertable'?
e.g.

DECLARE @temp TABLE (col1 INT, col2 INT, RowID int IDENTITY)
    INSERT INTO @temp (col1, col2) 
    SELECT Col1, Col2 FROM MyTable WHERE Col1 BETWEEN @lower AND @upper ORDER BY Col2; 

insert into othertable(col1, col2, Position) select col1, col2, RowID from @temp
select col1, col2 from @temp

Sorry, SQL isn't my first language, or indeed second or third!

This way, rather than the other way you suggested. Create the temp table or table variable with an identity column.

Also, please take note of Neilus' comments regarding whether to use table variables vs Temporary tables - I recently had a performance bottleneck using a table variable to store a temporary result set of around 2000 records (which I consider to be a small result set) - please try both ways to see what works best in your situation.

Be aware that the temporary table or table variable objects will be destroyed after each execution...not sure what you are trying to do with the 'Row Number' from the temp table - but this will re-start at whatever seed value you specify (or default to 1) - so it would be possible to get the same record inserted to the temp table/table variable with the same RowID if the query was executed twice, or, if the structure of the data in 'MyTable' allowed, using different values for @lower and @upper. What I'm trying to say is you cannot use the combination of Col1, Col2 and Position in 'othertable' to uniquely identify records if that's what the aim was.