Master of None

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

Tiny URL for this post:
 Copy