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