A Very Basic SQL Cursor for SQL Server (T-SQL)

Here’s a quick overview video of how to write and use a basic cursor in SQL Server’s T-SQL. The SQL code for both a regular SELECT cursor as well as an UPDATE cursor is below.

Cursors are really useful if you want to perform a specific action on all of the rows in a particular database table. They are especially well suited to task-based rather than set-based processes that have to call an external function or resource on each row, for example calling a component to add data to a messaging queue or send an email.

The downside to cursors is that their performance can be slow, so it’s best to avoid using them on very large tables or when query performance is important.

Cursors used to be one of the only ways of performing certain actions on every row in a table. However, be aware that there are alternatives, such as using a WHILE EXISTS loop or the more recent T-SQL Common Table Expressions (CTE).

Incidentally, in both examples I use the SQL Server Northwind sample database. Here’s a video that shows you how to install this really useful test database in SQL Server Management Studio:

Basic SQL Cursor for a SELECT Statement

DECLARE @ProductName NVARCHAR(40)
DECLARE @DiscontinuedProducts NVARCHAR(4000)

SET @DiscontinuedProducts = ‘Discontinued Products…’

DECLARE mycursor CURSOR FOR
SELECT productname
FROM   products
WHERE  discontinued = 1

OPEN mycursor

FETCH next FROM mycursor INTO @ProductName

WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @DiscontinuedProducts = @DiscontinuedProducts + @ProductName + ‘,’

FETCH next FROM mycursor INTO @ProductName
END

CLOSE mycursor
DEALLOCATE mycursor

Basic SQL Cursor with Table UPDATE

DECLARE @ProductID INT
DECLARE @Discontinued BIT
DECLARE @UnitsInStock SMALLINT

SET nocount ON

DECLARE mycursor CURSOR FOR
SELECT productid,
unitsinstock,
discontinued
FROM   products

OPEN mycursor
FETCH next FROM mycursor INTO @ProductID, @UnitsInStock, @Discontinued

WHILE @@FETCH_STATUS = 0
BEGIN
IF @Discontinued = 1
BEGIN
UPDATE products
SET    productstatus = ‘DISCONTINUED’
WHERE  productid = @ProductID
END
ELSE
BEGIN
IF @UnitsInStock > 0
BEGIN
UPDATE products
SET    productstatus = ‘IN STOCK’
WHERE  productid = @ProductID
END
ELSE
BEGIN
UPDATE products
SET    productstatus = ‘OUT OF STOCK’
WHERE  productid = @ProductID
END
END

FETCH next FROM mycursor INTO @ProductID, @UnitsInStock, @Discontinued
END

CLOSE mycursor

DEALLOCATE mycursor
–SELECT * FROM Products