Someone who’s pretty good programming on the front end, but not so crash hot on the backend, eg SQL Server, asked me the other day about looping through recordsets in a SQL Server Stored Procedure.
It is of course possible to use a cursor and FETCH but most SQL programmers frown on cursors in stored procedures. Cursors involve grabbing a bunch of records and locking them, there can be a substantial performance hit. They should really only be used as a last resort, for example, where you don’t have a good enough unique key value on your rows (although there are ways around that one usually as well).
The easiest way I know to look through records is to employ a temporary table, here’s a simple example:
-- Create a temporary table to hold the records to be updated DECLARE @uniqueId int DECLARE @TEMP TABLE (uniqueId int) -- Insert into the temporary table a list of the records to be updated INSERT INTO @TEMP (uniqueId) SELECT uniqueId FROM myTable -- Start looping through the records WHILE EXISTS (SELECT * FROM @TEMP) BEGIN -- Grab the first record out SELECT Top 1 @uniqueId = uniqueId FROM @TEMP PRINT 'Working on @uniqueId = ' + CAST(@uniqueId as varchar(100)) -- Perform some update on the record UPDATE myTable SET myField = 'something or other' WHERE uniqueId = @uniqueId -- Drop the record so we can move onto the next one DELETE FROM @TEMP WHERE uniqueId = @uniqueId END
I was doing a bit of research how to do that without a cursor, writing a new stored procedure to copy data from 2 tables… and found that post on your blog, the web is definitely a small world 🙂
Hi Arthur, that’s funny!
Thanks this example helped me with a tricky problem…
it has been really helpful , I had to call a procedure to execute each time it runs through the loop.
I was looking for a way to avoid using cursors in my stored procedure and the example in your article worked perfectly! Thanks a lot!
Thanks, this helped! 🙂
Pingback: I need to write more blog posts about SQL server and missing toolbars in Excel | David Eedle
Great… simple and effective solution. Thanks a lot..
helpful post, thnx !
thanks it really helped me
good one !
Thanks! I’ve seen tons of different examples, and never understood the process until your example! This is going to help me out GREATLY now and in the future!
Thank you. it was very useful.