How-To: Create Cursor in TSQL
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]:
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
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.
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
:
:
: