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.