In T-SQL stored procedures and user-defined functions, there will be times when you need to iterate through a recordset/table, to perform some operation on each record.
That’s what cursors are made for. Except that they don’t scale at all, so you should never use them for sets of more than a few dozen records.
What you can do instead is to “build your own cursor” using a temporary table or table variable, which does scale, even to processing millions of records:
DECLARE @Count int DECLARE @AccountID char(12) DECLARE @UserField1 varchar(128) CREATE TABLE #tblAccount ( [AccountID] [char](12), [UserField1] [varchar](128) ) CREATE INDEX PIndex ON #tblAccount (AccountID) INSERT INTO #tblAccount SELECT AccountID FROM sysdba.Account ORDER BY AccountID SELECT @Count = Count(*) FROM #tblAccount WHILE @Count > 0 BEGIN SET @UserField1 = iCount SELECT @AccountID = MIN(AccountID) FROM #tblAccount UPDATE sysdba.Account SET UserField1 = @UserField1 WHERE AccountID = @AccountID DELETE FROM #tblAccount WHERE AccountID = @AccountID SET @Count = @Count - 1 END DROP TABLE #tblAccount
Deleting each record after you’ve processed it speeds up the processing by (IIRC) around an order of magnitude. Using a temporary table and putting an index on the field you’re using for the primary key has similarly positive effects. (You can’t explicitly put an index on a table variable, but you can do so implicitly just by setting a primary key for it.)