{"id":133,"date":"2010-02-03T11:52:51","date_gmt":"2010-02-03T17:52:51","guid":{"rendered":"https:\/\/jackdonnell.com\/?p=133"},"modified":"2016-01-20T16:50:33","modified_gmt":"2016-01-20T22:50:33","slug":"t-sql-using-paramter-table-like-a-cursor","status":"publish","type":"post","link":"https:\/\/jackdonnell.com\/?p=133","title":{"rendered":"T-SQL Using Parameter Table like a Cursor"},"content":{"rendered":"

The Google gods seem to like a very old page of mine about using Cursors in T-SQL<\/a>. So, from time to time\u00a0I get commnets via email.\u00a0 Thought\u00a0I \u00a0would share an alternative to using cursors.<\/p>\n

<\/p>\n

Here is a bit of code works like a cursor, but perhaps a bit more less intense on your SQl Server System.\u00a0\u00a0I cobbled it pretty quick and it seems to run ok on SQL Server 2005.<\/p>\n

Thanks – Jack<\/p>\n

USE MASTER
\nGo
\n\/*
\nCONSUME PARAMETER TABLE Like a CURSOR
\nJCD 2-3-2010<\/p>\n

SWITCH TO TEMP Table when working
\nwith large Data sets<\/p>\n

*\/
\nDECLARE\u00a0 @ID INT — Begining Record
\n,@maxID INT — Max ID from Parameter Table
\n— DECLARE\/CREATE THE TABLE<\/p>\n

DECLARE @ObjectTable TABLE(
\nID INT IDENTITY(1,1) NOT NULL
\n,ObjectName VARCHAR(75) NOT NULL
\n,SumofallChars BIGINT DEFAULT (0)
\n)<\/p>\n

\/*
\nTo replace with Temp Table
\nA. TEST for #ObjectTable Table and DROP
\nand Create #ObjectTable<\/p>\n

IF OBEJCT_ID(‘tempdb.dbo.#ObjectTable’,’u’) IS NOT NULL
\nBEGIN
\nDROP #ObjectTable
\nEND<\/p>\n

BEGIN
\nCREATE TABLE #ObjectTable
\n(
\nID INT IDENTITY(1,1) NOT NULL
\n,ObjectName VARCHAR(75) NOT NULL
\n,SumofallChars BIGINT DEFAULT (0)
\n)\ufffd
\nEND<\/p>\n

B. Then replace all the references to #ObjectTable<\/p>\n

*\/<\/p>\n

— POPULATE PARMETER TABLE
\n— Just Grabbing top 15 Records
\nINSERT INTO @ObjectTable (ObjectName)
\nSELECT DISTINCT
\nTOP 15
\nRTRIM([name]) as [ObjectName]
\nFROM\u00a0\u00a0 sys.sysobjects where type=’s’
\nORDER BY\u00a0 RTRIM([name])
\n— Part That uses the Table Row By Row
\n— Find the number or Rows
\nSELECT @ID = 1
\n,@MaxID = MAX(ID)
\nFROM @ObjectTable<\/p>\n

— Test For Empty Data Set<\/p>\n

If (Select Count(1)\u00a0FROM\u00a0 @ObjectTable) = 0
\nBEGIN
\nPRINT ‘No Records Found’
\nGOTO ENDPROC
\nEND
\n\/*
\nDisplay ALL Data
\nRETURN all Rows in PARAMETER TABLE
\nNormall Comment out except for Debugging
\n*\/
\nSELECT ID, ObjectName , SumofallChars
\nFROM\u00a0 @ObjectTable
\nORDER BY ID
\n— Start to Loop through records<\/p>\n

WHILE @ID <= @MaxID
\nBEGIN
\nSET NOCOUNT ON<\/p>\n

— Bit of Code to Pretend this is useful
\n\/*
\nA. Select Data
\nB. Update Data Row
\n1. reverse Name
\n2. Create a Cumlative Sount of all objectname
\nCharacters then add length of current row
\n3. Return Data
\n*\/<\/p>\n

—Do a Weird Useless update
\nUPDATE\u00a0 @ObjectTable
\nSET [ObjectName] = REVERSE([ObjectName])
\n,SumofallChars = (SELECT SUM(SumofallChars) from @ObjectTable )\u00a0 + LEN(ObjectName)
\nFROM @ObjectTable
\nWHERE ID = @ID<\/p>\n

— Increment @ID to step through Records
\nSET @ID=@ID +1
\nEND
\n— RETURN ‘Updated’ Rows in PARAMETER TABLE
\nSELECT\u00a0 ID, ObjectName , SumofallChars
\nFROM\u00a0 @ObjectTable
\nORDER BY ID DESC
\nENDPROC:<\/p>\n

GO<\/p>\n","protected":false},"excerpt":{"rendered":"

Searching Google about Cursors in t-SQl might get this result https:\/\/jackdonnell.com\/articles\/SQL_CURSOR.htm. Here is an alternative<\/p>\n

Read more »<\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_exactmetrics_skip_tracking":false,"footnotes":""},"categories":[266,138,22,51,5],"tags":[218,261,373,315,249,35,36,295,291,343],"class_list":["post-133","post","type-post","status-publish","format-standard","hentry","category-dba","category-programming","category-sql-server","category-ssrs","category-t-sql","tag-cursor","tag-loop","tag-object_id","tag-query","tag-script","tag-select","tag-sql","tag-sql-server","tag-t-sql","tag-tempdb"],"_links":{"self":[{"href":"https:\/\/jackdonnell.com\/index.php?rest_route=\/wp\/v2\/posts\/133","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/jackdonnell.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/jackdonnell.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/jackdonnell.com\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/jackdonnell.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=133"}],"version-history":[{"count":8,"href":"https:\/\/jackdonnell.com\/index.php?rest_route=\/wp\/v2\/posts\/133\/revisions"}],"predecessor-version":[{"id":635,"href":"https:\/\/jackdonnell.com\/index.php?rest_route=\/wp\/v2\/posts\/133\/revisions\/635"}],"wp:attachment":[{"href":"https:\/\/jackdonnell.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=133"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/jackdonnell.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=133"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/jackdonnell.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=133"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}