Looping through records in SQL Server Stored Procedure

Posted on by

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

 

 

 

 

 

13 thoughts on “Looping through records in SQL Server Stored Procedure”

  1. 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 🙂
    Thanks David!
    Arthur

    Reply ↓

  5. 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!

    Reply ↓

  7. Pingback: I need to write more blog posts about SQL server and missing toolbars in Excel | David Eedle

  12. 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!

    Reply ↓

Leave a comment