Skip to content

How-To: Create Cursor in TSQL

January 21, 2009

I am sure you’ve been hearing or reading debates on why not to use CURSOR in SQL Server. I’m glad that is beyond the scope of this yet another How-to article here in my blog.

Although some people would say not to use cursor, I have noticed that lots of people are still asking for the basics of cursor. Well, this article is for those folks who want to know how to create a cursor in SQL Server using TSQL.

In order for you to successfully run this sample, you need the AdventureWorks database.

DECLARE @tablename VARCHAR(60)

DECLARE cursor_tablenames CURSOR FOR
SELECT name FROM AdventureWorks.sys.tables

OPEN cursor_tablenames
FETCH NEXT FROM cursor_tablenames INTO @tablename

WHILE @@FETCH_STATUS = 0
BEGIN

SELECT  t.name AS [TABLE Name], c.name AS [COLUMN Name], p.name AS [DATA Type], p.max_length AS[SIZE],   CAST(p.PRECISION AS VARCHAR) +'/'+ CAST(p.scale AS VARCHAR) AS [PRECISION/Scale]
FROM AdventureWorks.sys.objects AS t
JOIN AdventureWorks.sys.columns AS c
ON t.OBJECT_ID=c.OBJECT_ID
JOIN AdventureWorks.sys.types AS p
ON c.system_type_id=p.system_type_id
WHERE t.name = @tablename
AND t.type_desc='USER_TABLE'
ORDER BY t.name ASC

FETCH NEXT FROM cursor_tablenames INTO @tablename
END

CLOSE cursor_tablenames
DEALLOCATE cursor_tablenames

This will return the User Tables and their attributes in a single result set [See Comment by Josef – Marlon Ribunal]:

curosrdemo

Advertisements
5 Comments leave one →
  1. May 19, 2010 2:23 PM

    The concept of cursors have eluded me. But, your example brought it home. I’m very visual and you provided the needed illustration.
    Thanks.

  2. June 2, 2009 8:31 AM

    Is there parameter passing available in T-SQL cursor declaration like PL/SQL does?

    Declaration

    CURSOR CURSOR_NAME(P1 type, P2 type ….) IS
    SELECT COL1, COL2, COL3 … FROM table_name T
    WHERE (T.COL1 = P1) AND (T.COL2 = P2)
    ORDER BY COL3

    and when called

    FOR CN IN CURSOR_NAME(MY_P1, MY_P2) LOOP
    :
    :
    :

  3. January 21, 2009 1:02 PM

    There is nothing wrong with cursors, per se, but far too many developers who are used to doing things by iterating through a record set see cursors as the way to do something when SQL Server is set-oriented. I know a programmer who failed the code review because they rewrote a stored procedure to use output parameters rather than a result set, which is good, but they loaded the output parameters using a cursor because they didn’t know any better.

    Your example, while a valid cursor example, doesn’t address why someone would want to do this. What benefit does one get from a cursor for this? And, technically, it’s not returning a single result set. Execute that within any app and you’ll find you’re getting multiple result sets to iterate through.

    Just my .02

Trackbacks

  1. Automated SQL Server Back: Poor Man’s Edition « Dangling On The Shoulders Of Giants

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

  • Archives


  • RSS SQLServerPedia

  • RSS SQL Crunch – Hot SQL Links

  • My Bookmarks

  • Business Career DBA Tool Events free ebook Humor IT News Members Only Microsoft MySQL Operating System Oracle Personal Reporting Services SQL Server SQLServerPedia Syndication SSC SSIS TSQL Tutorial Uncategorized Video

  • Software Blogs - BlogCatalog Blog Directory
  • %d bloggers like this: