The Google gods seem to like a very old page of mine about using Cursors in T-SQL. So, from time to time I get commnets via email.  Thought I  would share an alternative to using cursors.

Here is a bit of code works like a cursor, but perhaps a bit more less intense on your SQl Server System.  I cobbled it pretty quick and it seems to run ok on SQL Server 2005.

Thanks – Jack

USE MASTER
Go
/*
CONSUME PARAMETER TABLE Like a CURSOR
JCD 2-3-2010

SWITCH TO TEMP Table when working
with large Data sets

*/
DECLARE  @ID INT — Begining Record
  ,@maxID INT — Max ID from Parameter Table
 – DECLARE/CREATE THE TABLE

DECLARE @ObjectTable TABLE(
    ID INT IDENTITY(1,1) NOT NULL
   ,ObjectName VARCHAR(75) NOT NULL
   ,SumofallChars BIGINT DEFAULT (0)
  )

/*
 To replace with Temp Table
A. TEST for #ObjectTable Table and DROP
   and Create #ObjectTable

 IF OBEJCT_ID(‘tempdb.dbo.#ObjectTable’,'u’) IS NOT NULL
 BEGIN
  DROP #ObjectTable
 END

 BEGIN
  CREATE TABLE #ObjectTable
 (
     ID INT IDENTITY(1,1) NOT NULL
    ,ObjectName VARCHAR(75) NOT NULL
    ,SumofallChars BIGINT DEFAULT (0)
   )�
 END

B. Then replace all the references to #ObjectTable
 

*/

– POPULATE PARMETER TABLE
 – Just Grabbing top 15 Records
INSERT INTO @ObjectTable (ObjectName)
 SELECT DISTINCT
  TOP 15
  RTRIM([name]) as [ObjectName]
 FROM   sys.sysobjects where type=’s’
 ORDER BY  RTRIM([name])
– Part That uses the Table Row By Row
– Find the number or Rows
SELECT @ID = 1
   ,@MaxID = MAX(ID)
FROM @ObjectTable

– Test For Empty Data Set

If (Select Count(1) FROM  @ObjectTable) = 0
BEGIN
 PRINT ‘No Records Found’
 GOTO ENDPROC
END
/*
Display ALL Data
RETURN all Rows in PARAMETER TABLE
Normall Comment out except for Debugging
*/
 SELECT ID, ObjectName , SumofallChars
 FROM  @ObjectTable
 ORDER BY ID
– Start to Loop through records

WHILE @ID <= @MaxID
BEGIN
 SET NOCOUNT ON

 – Bit of Code to Pretend this is useful
 /*
  A. Select Data
  B. Update Data Row
   1. reverse Name
   2. Create a Cumlative Sount of all objectname
    Characters then add length of current row
   3. Return Data
 */

 —Do a Weird Useless update
 UPDATE  @ObjectTable
 SET [ObjectName] = REVERSE([ObjectName])
  ,SumofallChars = (SELECT SUM(SumofallChars) from @ObjectTable )  + LEN(ObjectName)
 FROM @ObjectTable
 WHERE ID = @ID

 – Increment @ID to step through Records
 SET @ID=@ID +1
END
– RETURN ‘Updated’ Rows in PARAMETER TABLE
 SELECT  ID, ObjectName , SumofallChars
 FROM  @ObjectTable
 ORDER BY ID DESC
ENDPROC:

GO