{"id":56,"date":"2008-05-27T14:08:14","date_gmt":"2008-05-27T20:08:14","guid":{"rendered":"https:\/\/jackdonnell.com\/?p=56"},"modified":"2008-05-30T08:01:07","modified_gmt":"2008-05-30T14:01:07","slug":"t-sql-alternatives-to-using-cursors","status":"publish","type":"post","link":"https:\/\/jackdonnell.com\/?p=56","title":{"rendered":"T-SQL: Alternatives to Using Cursors"},"content":{"rendered":"

[UPDATE]<\/strong> — <\/em>SQLServerCentral.com<\/em><\/a>\u00a0has a great article( from 2002) on the use of Temp Tables. You may find the information useful.<\/em><\/p>\n

I have gotten a ton of hits some links <\/a>that I created years ago on T-SQL Cursors<\/a>. Sometimes they are the the best course of action , but may times they are not the most efficient. This posting has some a possible alternative.<\/p>\n

Creating Tables with Identity Columns<\/strong>
\nI like to create tables and temp tables with identity columns. You can then do row by row process the data using the id column. In another post, I will show you how to create and some uses for Table-Valued functions to provide similar utility found with using cursors. Oh, don’t forget our friend the Case Statement, either.<\/p>\n

<\/p>\n

Below is a an Example of using a temp table with an identity column:
\nI created a example database then created some very basic and useless views.
\nThe temp table is populated with the values from the sysobjects table for the views.<\/p>\n

I then step through the temp table using the identity column id and do a select on the type
\nand count of each of the views:
\n\ufffd
\n
\nUSE ExampleDB
\nGO
\n\/*
\nCreated a small datbase and created views of the
\nsysobjects table based upon the n<\/code><\/p>\n

*\/
\nIF OBJECT_ID (‘objects_start_with_sys’,’v’) IS NOT NULL
\nBEGIN
\nDROP VIEW dbo.objects_start_with_sys
\nEND
\nGO<\/p>\n

CREATE VIEW dbo.[objects_start_with_sys]
\nAS
\nSelect name ,type
\nfrom sysobjects where name like ‘sys%’
\nGO
\nIF OBJECT_ID (‘objects_start_with_ob’,’v’) IS NOT NULL
\nBEGIN
\nDROP VIEW objects_start_with_ob
\nEND
\nGO<\/p>\n

CREATE VIEW dbo.[objects_start_with_ob]
\nAS
\nSelect name ,type
\nfrom sysobjects where name like ‘ob%’
\nGO<\/p>\n

IF OBJECT_ID (‘objects_start_with_que’,’v’) IS NOT NULL
\nBEGIN
\nDROP VIEW objects_start_with_que
\nEND
\nGO
\nCREATE VIEW dbo.[objects_start_with_que]
\nAS
\nSelect name ,type
\nfrom sysobjects where name like ‘que%’
\nGO<\/p>\n

IF OBJECT_ID (‘objects_start_with_e’,’v’) IS NOT NULL
\nBEGIN
\nDROP VIEW objects_start_with_e
\nEND
\nGO
\nCREATE VIEW dbo.[objects_start_with_e]
\nAS
\nSelect name ,type
\nfrom sysobjects where name like ‘e%’
\nGO<\/p>\n

— Drop\/Create Temp Table with Identity Column for Order
\nIF OBJECT_ID (‘TEMPDB..#GetViews’,’u’) IS NOT NULL
\nBEGIN
\nDROP TABLE #GetViews
\nEND
\nBEGIN
\nCREATE TABLE #GetViews(
\nid INT IDENTITY(1,1),
\nviewName VARCHAR(40) NOT NULL
\n)
\nEND<\/p>\n

INSERT INTO #GetViews
\nSelect RTRIM(name)viewName from sysobjects
\nwhere type=’v’
\norder by NAME DESC<\/p>\n

SELECT * from #GetViews<\/p>\n

DECLARE @I INT , @MaxI INT<\/p>\n

SELECT @I = 1
\n,@MaxI = MAX(id) from #GetViews<\/p>\n

WHILE @I <= @MaxI
\nBEGIN
\nDECLARE @sqlStatement NVARCHAR(150)
\n— Build Select Statement
\nSELECT @sqlStatement = ‘Select type, Count(type) as [Count_items from_’+UPPER(RTRIM(viewName)) +’] from ‘ + RTRIM(viewName)+ ‘ Group By Type Order by Count(type) DESC’
\nfrom #GetViews where id = @I
\n— Run Select Statement
\nEXECUTE sp_ExecuteSQL @sqlStatement
\n— Increment @I to Next Value
\nSelect @I = @I +1
\nEND<\/p>\n

Select id
\n,viewname
\nfrom #GetViews order by viewname<\/p>\n

— Drop Temp Table with Identity Column for Order
\nIF OBJECT_ID (‘TEMPDB..#GetViews’,’u’) IS NOT NULL
\nBEGIN
\nDROP TABLE #GetViews
\nEND<\/p>\n","protected":false},"excerpt":{"rendered":"

[UPDATE] — SQLServerCentral.com\u00a0has a great article( from 2002) on the use of Temp Tables. You may find the information useful. I have gotten a ton of hits some links that I created years ago on T-SQL Cursors. Sometimes they are …<\/p>\n

Read more »<\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_exactmetrics_skip_tracking":false,"_exactmetrics_sitenote_active":false,"_exactmetrics_sitenote_note":"","_exactmetrics_sitenote_category":0,"footnotes":""},"categories":[138,5],"tags":[218,221,291,219,220],"class_list":["post-56","post","type-post","status-publish","format-standard","hentry","category-programming","category-t-sql","tag-cursor","tag-example","tag-t-sql","tag-temp","tag-views"],"_links":{"self":[{"href":"https:\/\/jackdonnell.com\/index.php?rest_route=\/wp\/v2\/posts\/56","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=56"}],"version-history":[{"count":0,"href":"https:\/\/jackdonnell.com\/index.php?rest_route=\/wp\/v2\/posts\/56\/revisions"}],"wp:attachment":[{"href":"https:\/\/jackdonnell.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=56"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/jackdonnell.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=56"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/jackdonnell.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=56"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}