Using SQL Server Cursors

I have recently found myself using cursors for quite a few processing tasks in SQL Server such as data takeons and cleanups. While there are packages...

I have recently found myself using cursors for quite a few processing tasks in SQL Server such as data takeons and cleanups. While there are packages such as SSIS (SQL Server Integration Services), most of the tasks I do are short and are able to be completed in a SQL script with minimal effort. This post will provide an overview of what SQL Server Cursors are and how to use them.

What are SQL Server Cursors?

A SQL Server Cursor allows us to iterate through a set of data in a sequential manner. This means that instead of performing set logic on data that is returned from a query (which is the most common type of logic used in SQL), we go through each record one by one and do something with that record – much like a for loop in code. This allows us to do processing that needs to operate on each row individually.

Example of using a SQL Server Cursor

In the following example we are going to be doing a data import of companies and people associated with each company. Right now the data has been imported into a staging table called ClientImport. We now need to write a script that will extract the data correctly and insert the data into the correct tables.

NOTE: I have created this example online on SQL Fiddle so you can go there and experiment with the actual script.

The Database

The database will have the following normalized structure:

The database will have the following structure.

We will then map the fields in the ClientImport table to the Company and Person table as follows:

SchemaMapping 1

The Script

The code for a cursor follows the following pattern:

  1. DECLARE the cursor
  2. OPEN the cursor to begin reading
  3. FETCH the NEXT record that needs to be processed
  4. Do whatever processing you need to do with the record
  5. Repeat the process from Step 3 to Step 5 until there are no records left to process (when @@FETCH_STATUS != 0)
  6. DEALLOCATE the cursor
  7. CLOSE the cursor

Below is a script that follows the above pattern. It uses the cursor to import the data into the correct tables and has detailed comments explaining how it is doing this at each step:

-- Declare variables for all the fields we want to extract from
-- the ClientImport table.
 
-- Company fields
DECLARE @CompanyName NVARCHAR(100);
DECLARE @RegistrationCode NVARCHAR(10);
-- CEO Fields
DECLARE @CEOFirstName NVARCHAR(50);
DECLARE @CEOMiddleNames NVARCHAR(100);
DECLARE @CEOLastName NVARCHAR(50);
DECLARE @CEOPhoneNumber NVARCHAR(13);
DECLARE @CEOEmailAddress NVARCHAR(255);
-- Contact Fields
DECLARE @ContactFirstName NVARCHAR(50);
DECLARE @ContactMiddleNames NVARCHAR(100);
DECLARE @ContactLastName NVARCHAR(50);
DECLARE @ContactPhoneNumber NVARCHAR(13);
DECLARE @ContactEmailAddress NVARCHAR(255);
 
-- Now that we have all the fields, we can create a cursor that
-- will select all the data from the ClientImport table
DECLARE curs CURSOR FOR
SELECT CompanyName,
       RegistrationCode,
       CEOFirstName,
       CEOMiddleNames,
       CEOLastName,
       CEOPhoneNumber,
       CEOEmailAddress,
       ContactFirstName,
       ContactMiddleNames,
       ContactLastName,
       ContactPhoneNumber,
       ContactEmailAddress
FROM   ClientImport;
 
-- Once we've declared the cursor, we need to open it
OPEN curs;
 
-- Now we need to fetch the fields from the current row that the
-- cursor is on and assign them to our variables
FETCH NEXT FROM curs INTO @CompanyName,
                          @RegistrationCode,
                          @CEOFirstName,
                          @CEOMiddleNames,
                          @CEOLastName,
                          @CEOPhoneNumber,
                          @CEOEmailAddress,
                          @ContactFirstName,
                          @ContactMiddleNames,
                          @ContactLastName,
                          @ContactPhoneNumber,
                          @ContactEmailAddress;
 
-- We keep looping while there are more records to process
-- (i.e. while the @@FETCH_STATUS is 0)
WHILE @@FETCH_STATUS = 0
BEGIN
    -- Now we do our processing for each record
 
    -- Insert a new Company record
    INSERT INTO Company (CompanyName, RegistrationCode)
    VALUES (@CompanyName, @RegistrationCode);
 
    -- Get the ID that was assigned to our company record we
    -- just inserted
    DECLARE @CompanyId INT;
    SELECT @CompanyId = SCOPE_IDENTITY();
 
    -- Add in the CEO only if their first and last name are not
    -- NULL (i.e. then we don't have information on them)
    IF @CEOFirstName IS NOT NULL AND @CEOLastName IS NOT NULL
    BEGIN
        INSERT INTO Person (FirstName, MiddleNames, LastName,
                            PhoneNumber, EmailAddress, CompanyId, Role)
        VALUES (@CEOFirstName, @CEOMiddleNames, @CEOLastName,
                @CEOPhoneNumber, @CEOEmailAddress, @CompanyId, 'CEO');
    END
 
    -- We always need a Contact person so we do not need to check
    -- if their FirstName and LastName is NULL
    INSERT INTO Person (FirstName, MiddleNames, LastName, PhoneNumber,
                        EmailAddress, CompanyId, Role)
    VALUES (@ContactFirstName, @ContactMiddleNames, @ContactLastName,
            @ContactPhoneNumber, @ContactEmailAddress, @CompanyId,
            'Contact');
 
    -- We need to then go and fetch the next record that we need to
    -- process and assign its fields to the appropriate variables
    FETCH NEXT FROM curs INTO @CompanyName,
                              @RegistrationCode,
                              @CEOFirstName,
                              @CEOMiddleNames,
                              @CEOLastName,
                              @CEOPhoneNumber,
                              @CEOEmailAddress,
                              @ContactFirstName,
                              @ContactMiddleNames,
                              @ContactLastName,
                              @ContactPhoneNumber,
                              @ContactEmailAddress;
END
 
-- Once we are done with the cursor, we need to close it and
-- then deallocate it
CLOSE curs;
DEALLOCATE curs;
GO
 
-- Empty our staging table so we don't import the data again
DELETE FROM ClientImport;
GO
 
-- Check to see if the data was imported
SELECT * FROM Company;
GO
 
SELECT * FROM Person;
GO

Again it is worth noting that this whole example can be found online on SQL Fiddle. You can also download the scripts to create the schema and do the processing here.

Final Thoughts

Hopefully the above example helped you understand how and when cursors can be used in SQL Server. Depending on your working environment, cursors may or may not be favoured as there are often disagreements regarding whether they should be used since SQL is primarily based on set logic. I believe in using the correct tool to do a job in a simple, well-performing and easy-to-understand manner. In cases like the example above, the task is simple enough to do with a cursor instead of creating an entire SSIS package or something similar. In some other cases it may be better to create an SSIS package to do the data import. It’s also important to be wary that since cursors do use sequential logic and not set logic, combining the two types of logic may produce strange side-effects that can be challenging to debug. In the end, it’s all up to your discretion – just Keep It Simple (KIS) and performant.