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]:
5 Comments
leave one →
The concept of cursors have eluded me. But, your example brought it home. I’m very visual and you provided the needed illustration.
Thanks.
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
:
:
:
Sorry for the very, very late response, Josef. I actually used the sample above in one of my reports. What I was doing was dumping the results to a temp table and using that temp table as a source for my SSRS report. I remove this piece from the above example that is why it does not seem to make sense. Maybe I should write a new blog entry about using temp table as a data source for a SSRS report.
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