SQL Server Performance Forum – Threads Archive
Retrieving data from cursor
I have a stored procedure which opens a very generic cursor (based on sql built from the supplied procedure parameters). The problem im having is this is very generic and so the field list often varies.Currently im having to write dynamic sql to define corresponding local variables and then loading the data from the cursor into these locals. This is a bit of a pain, Im wondering if there is any way I can access the row the cursor is pointing to directly (by fieldname), without having to FETCH INTO some locals? Thanks.
I don’t think you can do this. However, maybe you can rewrite the procedure to not use a cursor. I always try to avoid cursors whenever possible.
"How do you expect to beat me when I am forever?"
ok, thanks royy. Sadly I wish it was possible to rewrite without cursors. The sp takes dynamic SQL which is then executed, and a counter is then tallied for the rows in order (imagine a series of bets on horses, the sp needs to tally the biggest winning and losing streak, which is dependant on the order the records are processed, hence requires a cursor).
Have you tried temp tables instead of a cursor?
——————
Brad M. McGehee
Webmaster
SQL-Server-Performance.Com
Im not sure thats really possible. I could fill a temp table but then id still have to open that with a cursor so I can find the longest losing streak (chronologically), where each row in the dataset corresponds to a bet which either wins or loses. Unless im missing something <img src=’/community/emoticons/emotion-1.gif’ alt=’
‘ /><br /><br />
Have you considered the possiblity of looping through a temp table using WHILE statement. Don’t know if it will work in your case, just something else to consider. This way, you might be able to get rid of the cursor.
——————
Brad M. McGehee
Webmaster
SQL-Server-Performance.Com
I hadnt considered that, but it looks like it could be very useful. Thanks for the tip!
Instead of a cursor, no matter how tempting, I always use a temp table with a WHILE loop. Cursors are known for their performance problems, and unless I have some code that is only going to run once, I never use cursors. The WHILE code looping through a temp table is a more valid solution now because there are table variables in SQL 2000 which are in memory and really fast!
"How do you expect to beat me when I am forever?"
Hi I just posted a question about CURSORS in the "Performance Tunning for DBA" and looks like I should have done it here also. Would you mind giving a sample of how a curser and be placed with a WHILE statement. Thanks
]]>