Scroll Cursor in SQL Server Examples

By: Anirudh Kumar Bhudhiraj | Updated: 2022-10-14 | Comments (2) | Related: 1 | 2 | 3 | 4 | 5 | 6 | > TSQL

Problem

In this article, we will explore how to use SQL Server scroll cursors to selectively pick a row from the cursor instead of looping through rows one by one.

Solution

A SQL Server cursor is T-SQL logic to loop through a finite number of rows which are determined by the query that is used to load the cursor. Cursors are mainly used to perform sequential operations on the rows. The cursor can be viewed as a data set or a list of data which can be looped through for logical operations. In SQL Server, cursors are generally used to iterate through the entire loop sequentially, but with a scroll cursor there is the ability to access specific rows from the cursor which we will discuss in this article.

Create Sample Tables and Data

First, let's create some sample tables and data for the cursor examples.

CREATE TABLE [dbo].[Employees](
   [Id]              [int] NULL,
   [Firstname]       [varchar](50) NULL,
   [Lastname]        [varchar](50) NULL,
   [Phone]           [varchar](50) NULL,
   [Email]           [varchar](50) NULL,
   [CountryId]       [varchar](50) NULL,
   [Dateofbirth]     [date] NULL,
   [DepartmentId]    [int] NULL,
   [Secondary_Phone] [varchar](255) NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Project_Details](
   [Emp_Id]      [int] NULL,
   [ProjectName] [varchar](50) NULL,
   [ProjectDesc] [varchar](50) NULL,
   [ProjectCost] [int] NULL,
   [Project_Id]  [int] NULL
) ON [PRIMARY]
GO

-- insert sample Employees data
INSERT [dbo].[Employees] ([Id], [Firstname], [Lastname], [Phone], [Email], [CountryId], [Dateofbirth], [DepartmentId], [Secondary_Phone]) 
   VALUES (10, N'John', N'Reacher', N'87169', N'[email protected]', N'100', CAST(N'1987-10-04' AS Date), 80, N'878763')
INSERT [dbo].[Employees] ([Id], [Firstname], [Lastname], [Phone], [Email], [CountryId], [Dateofbirth], [DepartmentId], [Secondary_Phone]) 
   VALUES (11, N'Sam', N'Smith', N'87168', N'[email protected]', N'101', CAST(N'1987-10-09' AS Date), 81, N'878764')
INSERT [dbo].[Employees] ([Id], [Firstname], [Lastname], [Phone], [Email], [CountryId], [Dateofbirth], [DepartmentId], [Secondary_Phone]) 
   VALUES (12, N'Ted', N'Mosby', N'87167', N'[email protected]', N'102', CAST(N'1986-10-09' AS Date), 82, N'878765')
INSERT [dbo].[Employees] ([Id], [Firstname], [Lastname], [Phone], [Email], [CountryId], [Dateofbirth], [DepartmentId], [Secondary_Phone]) 
   VALUES (13, N'Nelson', N'Glen', N'87170', N'[email protected]', N'103', CAST(N'1985-10-08' AS Date), 83, N'878766')
INSERT [dbo].[Employees] ([Id], [Firstname], [Lastname], [Phone], [Email], [CountryId], [Dateofbirth], [DepartmentId], [Secondary_Phone]) 
   VALUES (14, N'Ash', N'Grey', N'87171', N'[email protected]', N'104', CAST(N'1988-10-04' AS Date), 83, N'878767')
GO

-- insert sample Project_Details data
INSERT [dbo].[Project_Details] ([Emp_Id], [ProjectName], [ProjectDesc], [ProjectCost], [Project_Id]) 
   VALUES (10, N'Finace', N'Payroll Software Rollouts', 10000, 100)
INSERT [dbo].[Project_Details] ([Emp_Id], [ProjectName], [ProjectDesc], [ProjectCost], [Project_Id]) 
   VALUES (10, N'HR', N'HR Software Rollouts', 20000, 101)
INSERT [dbo].[Project_Details] ([Emp_Id], [ProjectName], [ProjectDesc], [ProjectCost], [Project_Id]) 
   VALUES (10, N'Inventory', N'Inventory Software Rollouts', 30000, 102)
INSERT [dbo].[Project_Details] ([Emp_Id], [ProjectName], [ProjectDesc], [ProjectCost], [Project_Id]) 
   VALUES (11, N'Ledgers', N'Ledger Software Rollouts', 3000, 103)
INSERT [dbo].[Project_Details] ([Emp_Id], [ProjectName], [ProjectDesc], [ProjectCost], [Project_Id]) 
   VALUES (11, N'Marketing', N'Marketing Software Rollouts', 40000, 104)
INSERT [dbo].[Project_Details] ([Emp_Id], [ProjectName], [ProjectDesc], [ProjectCost], [Project_Id]) 
   VALUES (11, N'Risk Management', N'Risk Mgmt Software Rollouts', 5000, 105)
INSERT [dbo].[Project_Details] ([Emp_Id], [ProjectName], [ProjectDesc], [ProjectCost], [Project_Id]) 
   VALUES (12, N'Ledger', N'Ledger Software Rollouts', 3000, 103)
INSERT [dbo].[Project_Details] ([Emp_Id], [ProjectName], [ProjectDesc], [ProjectCost], [Project_Id]) 
   VALUES (12, N'Marketing', N'Marketing Software Rollouts', 40000, 104)
INSERT [dbo].[Project_Details] ([Emp_Id], [ProjectName], [ProjectDesc], [ProjectCost], [Project_Id]) 
   VALUES (13, N'Risk Management', N'Risk Mgmt Software Rollouts', 5000, 105)
INSERT [dbo].[Project_Details] ([Emp_Id], [ProjectName], [ProjectDesc], [ProjectCost], [Project_Id]) 
   VALUES (14, N'Utility', N'Utility Software Rollouts', 45000, 106)
GO

Here is what the Employees data looks like.

Here is what the Project_Details data looks like.

Cursor Query

To interact with the data, we need to use a query that defines the record set for the cursor.

For the examples, we will use this query.

SELECT 
   b.id, 
   b.Firstname, 
   b.Lastname, 
   a.ProjectName, 
   a.ProjectDesc, 
   a.ProjectCost
FROM Project_Details a
  INNER JOIN Employees b ON b.Id = a.Emp_id
ORDER BY b.id;

Here are the results.

To help identify the rows easier for our example cursors, we will use the ROWNUMBER function, so each row has a unique row number. This will just make it easier to identify the rows in the output from the cursors. Note, the ROWNUMBER function does not need to be used, but it will help in the output you will see from the cursors in the examples below.

SELECT ROW_NUMBER() OVER (order by b.id) AS 'RowNumber', 
   b.id, 
   b.Firstname, 
   b.Lastname, 
   a.ProjectName, 
   a.ProjectDesc, 
   a.ProjectCost
FROM Project_Details a
  INNER JOIN Employees b ON b.Id = a.Emp_id
ORDER BY b.id;

SQL Cursor to Loop Through All Rows

This is the cursor code that will allow us to sequentially loop through the data, by using the FETCH NEXT command. Defining the cursor this way, it is defined as a forward only cursor, meaning you can only move forward row by row. We are also making it a READ_ONLY cursor, so updates cannot be made to the cursor.

This is the line in the code to define the cursor: DECLARE MyCursor CURSOR READ_ONLY FOR

DECLARE @RowNumber varchar(max);
DECLARE @Field1 varchar(max);
DECLARE @Field2 varchar(max);
DECLARE @Field3 varchar(max);
DECLARE @Field4 varchar(max);

BEGIN
   -- set the variable to the query used for the cursor
   DECLARE MyCursor CURSOR READ_ONLY FOR
      SELECT ROW_NUMBER() OVER (order by b.id) AS 'RowNumber', b.id, b.Firstname, b.Lastname, a.ProjectName
      FROM Project_Details a
        INNER JOIN Employees b ON b.Id = a.Emp_id
      ORDER BY b.id;
  
   OPEN MyCursor;

   FETCH NEXT FROM MyCursor INTO @RowNumber, @Field1, @Field2, @Field3, @Field4;

   WHILE @@FETCH_STATUS = 0
   BEGIN
      PRINT 'RowNumber:' + @RowNumber + ', ID:' + @Field1 + ', Employee:' + @Field2 + ' ' + @Field3 + ', Project:' + @Field4;
   
      FETCH NEXT FROM MyCursor INTO @RowNumber, @Field1, @Field2, @Field3, @Field4;
   END; 
 
   CLOSE MyCursor;
   DEALLOCATE MyCursor;
END;

Results.

We can see that each row is displayed above.

SQL Scroll Cursor to Selectively Pick Rows

In this example, we will use a scroll cursor and use the following items to selectively choose the record to work with instead of looping through rows one by one.

FETCH FIRST: Moves to the first record.
FETCH LAST: Moves the cursor to the last record of the result set.
FETCH ABSOLUTE: Moves the cursor to the specified record 'n' of the result set, where n is the number of rows.
FETCH RELATIVE: Moves the cursor to 'n' rows after the current row, where n is the number of rows.
FETCH NEXT: Moves to the next record.
FETCH PRIOR: Moves the cursor before last fetch of the result set. Note: there will be no result for the first fetch because it will position the cursor before the first row.

To defined the cursor, we use the SCROLL keyword and we are also making it a READ_ONLY cursor, so updates cannot be made to the cursor.

This is the line in the code to define the cursor: DECLARE MyCursor CURSOR SCROLL READ_ONLY FOR

DECLARE @RowNumber varchar(max);
DECLARE @Field1 varchar(max);
DECLARE @Field2 varchar(max);
DECLARE @Field3 varchar(max);
DECLARE @Field4 varchar(max);

BEGIN
   -- set the variable to the query used for the cursor
   DECLARE MyCursor CURSOR SCROLL READ_ONLY FOR
      SELECT ROW_NUMBER() OVER (order by b.id) AS 'RowNumber', b.id, b.Firstname, b.Lastname, a.ProjectName
      FROM Project_Details a
        INNER JOIN Employees b ON b.Id = a.Emp_id
      ORDER BY b.id;
  
   OPEN MyCursor;

   --FETCH LAST
   FETCH LAST FROM MyCursor INTO @RowNumber, @Field1, @Field2, @Field3, @Field4;
   PRINT 'RowNumber:' + @RowNumber + ', ID:' + @Field1 + ', Employee:' + @Field2 + ' ' + @Field3 + ', Project:' + @Field4

   --FETCH ABSOLUTE
   FETCH ABSOLUTE 5 FROM MyCursor INTO @RowNumber, @Field1, @Field2, @Field3, @Field4;
   PRINT 'RowNumber:' + @RowNumber + ', ID:' + @Field1 + ', Employee:' + @Field2 + ' ' + @Field3 + ', Project:' + @Field4
   
   --FETCH RELATIVE
   FETCH RELATIVE 3 FROM MyCursor INTO @RowNumber, @Field1, @Field2, @Field3, @Field4;
   PRINT 'RowNumber:' + @RowNumber + ', ID:' + @Field1 + ', Employee:' + @Field2 + ' ' + @Field3 + ', Project:' + @Field4
      
   --FETCH PRIOR
   FETCH PRIOR FROM MyCursor INTO @RowNumber, @Field1, @Field2, @Field3, @Field4;
   PRINT 'RowNumber:' + @RowNumber + ', ID:' + @Field1 + ', Employee:' + @Field2 + ' ' + @Field3 + ', Project:' + @Field4

   --FETCH FIRST
   FETCH FIRST FROM MyCursor INTO @RowNumber, @Field1, @Field2, @Field3, @Field4;
   PRINT 'RowNumber:' + @RowNumber + ', ID:' + @Field1 + ', Employee:' + @Field2 + ' ' + @Field3 + ', Project:' + @Field4
 
   --FETCH NEXT
   FETCH NEXT FROM MyCursor INTO @RowNumber, @Field1, @Field2, @Field3, @Field4;
   PRINT 'RowNumber:' + @RowNumber + ', ID:' + @Field1 + ', Employee:' + @Field2 + ' ' + @Field3 + ', Project:' + @Field4
    
   CLOSE MyCursor;
   DEALLOCATE MyCursor;
END;

Results:

Here is a breakdown of how these rows were selected:

RowNumber 10 = LAST
RowNumber 5 = ABSOLUTE 5 (go to 5th row)
RowNumber 8 = RELATIVE 3 (cursor is at row 5 plus 3 more rows)
RowNumber 7 = PRIOR
RowNumber 1 = FIRST
RowNumber 2 = NEXT

Next Steps

Check out the following articles:

About the author

MSSQLTips author Anirudh Kumar Bhudhiraj

Anirudh Kumar Bhudhiraj is a Full stack developer, developing GUI applications, web-APIs, microservices, and databases(SQL & No-SQL).

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2022-10-14

Scroll Cursors in SQL Server