Transact-SQL Cursor Example

Microsoft SQL Server comes with scripting capabilities as part of their Structured Query Language (SQL) implementation. The Microsoft and Sybase query languages are both referred to as “Transact-SQL” or T-SQL since MS SQL was originally a licensed fork of the Sybase database engine.

These scripting features allow database administrators and users to go beyond standard relational database queries to build general-purpose database tools. Common SQL scripting use cases include the automation of repetitive or complex database administration tasks.

Here I would like to demonstrate one of the T-SQL scripting features known as a CURSOR. If you search for “tsql cursor” online, you will likely find a lot of negative comments about T-SQL Cursors. I’m sure there are many reasons for this negativity and I agree that if there is a better SQL command for some task, you should *not* use a cursor.

I believe the case demonstrated here is a good example where the CURSOR is useful and one of the few ways to accomplish the task. This is a helpful technique to print useful output for a small query result set within a larger SQL script where you’re using the T-SQL PRINT statement to show useful output of the script actions and progress.

SET NOCOUNT ON; -- hide "rows affected" messages
DECLARE @myUname AS varchar(max);
DECLARE unameCur CURSOR FAST_FORWARD FOR
	SELECT username FROM myDB.dbo.person WHERE manager = 'ManagerName';
OPEN unameCur;
FETCH NEXT FROM unameCur INTO @myUname;
WHILE @@FETCH_STATUS = 0 BEGIN;
	PRINT 'Username = ' + @myUname;
	FETCH NEXT FROM unameCur INTO @myUname;
END;
CLOSE unameCur;
DEALLOCATE unameCur;

The standard cursor code may not be pretty or concise, but it is a solid technique to accomplish “for each record in result-set do x” with T-SQL. The pattern is simple and repeatable – it should look similar for standard queries and action code blocks, just copy – paste – and tweak as necessary.

See the Microsoft TechNet documentation for DECLARE CURSOR (Transact-SQL) for information about the important FAST_FORWARD option and other details that will help you know when and how to best use the T-SQL Cursor feature.

Advertisements

About notesbytom

Keeping technology notes on WordPress.com to free up my mind to solve new problems rather than figuring out the same ones repeatedly :-).
This entry was posted in Database and tagged , , , , . Bookmark the permalink.

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