Cursor Alternative in SQL Server

Introduction

A Cursor is a database object that retrieves data from a result set row by row. A Cursor is required whenever we need to process records row by row. A cursor impacts database performance. The Cursor forces the database engine to repeatedly fetch the rows, managing the locks and transmit the results. Forward-only and read-only cursors are faster and use the least resources. Cursor use the memory of the SQL Server instance and network bandwidth and also locks server resources.

Example

Suppose I have EmployeeMaster and EmployeeSalaryDetails Tables. The EmployeeMaster table contains details, like Name, code, email address and so on. And the EmployeeSalaryDetail s Table contains information like monthly total salary of each employee. Now I want to send an email to each employee for there salary for the given month.

Prerequisite tables, Stored Procedures and sample data

The following script helps to generate the table with test data and a Stored Procedure.

Create EmployeeMaster and EmployeeSalaryDetails tables:

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[EmployeeMaster]') AND type in (N'U'))

DROP TABLE [dbo].[EmployeeMaster]

CREATE TABLE [dbo].[EmployeeMaster](

[EmployeeId] [int] IDENTITY(1,1) NOT NULL,

[EmployeeCode] [varchar](25) NULL,

[EmployeeName] [varchar](50) NULL,

[EmailAddress] [varchar](50) NULL,

CONSTRAINT [PK_EmployeeMaster] PRIMARY KEY CLUSTERED

(

[EmployeeId] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_EmployeeSalaryDetails_EmployeeMaster]') AND parent_object_id = OBJECT_ID(N'[dbo].[EmployeeSalaryDetails]'))

ALTER TABLE [dbo].[EmployeeSalaryDetails] DROP CONSTRAINT [FK_EmployeeSalaryDetails_EmployeeMaster]

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[EmployeeSalaryDetails]') AND type in (N'U'))

DROP TABLE [dbo].[EmployeeSalaryDetails]

CREATE TABLE [dbo].[EmployeeSalaryDetails](

[SalaryDetailId] [int] IDENTITY(1,1) NOT NULL,

[EmployeeId] [int] NOT NULL,

[Month] [int] NOT NULL,

[TotalSalary] [float] NOT NULL,

CONSTRAINT [PK_EmployeeSalaryDetails2] PRIMARY KEY CLUSTERED

(

[SalaryDetailId] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

ALTER TABLE [dbo].[EmployeeSalaryDetails] WITH CHECK ADD CONSTRAINT [FK_EmployeeSalaryDetails_EmployeeMaster] FOREIGN KEY([EmployeeId])

REFERENCES [dbo].[EmployeeMaster] ([EmployeeId])

ALTER TABLE [dbo].[EmployeeSalaryDetails] CHECK CONSTRAINT [FK_EmployeeSalaryDetails_EmployeeMaster]

GO

Insert to test data

INSERT [dbo].[EmployeeMaster] ([EmployeeCode], [EmployeeName], [EmailAddress]) VALUES (N'A001', N'Jignesh Trivedi', N'[email protected]')

INSERT [dbo].[EmployeeMaster] ([EmployeeCode], [EmployeeName], [EmailAddress]) VALUES (N'A002', N'Tejas Trivedi', N'[email protected]')

INSERT [dbo].[EmployeeMaster] ([EmployeeCode], [EmployeeName], [EmailAddress]) VALUES (N'A003', N'Rakesh Trivedi', N'[email protected]')

INSERT [dbo].[EmployeeSalaryDetails] ([EmployeeId], [Month], [TotalSalary]) VALUES (1, 9, 5666.32)

INSERT [dbo].[EmployeeSalaryDetails] ([EmployeeId], [Month], [TotalSalary]) VALUES (2, 9, 10035.56)

INSERT [dbo].[EmployeeSalaryDetails] ([EmployeeId], [Month], [TotalSalary]) VALUES (3, 9, 3560)

Stored procedure for sending email

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MailNotification]') AND type in (N'P', N'PC'))

DROP PROCEDURE [dbo].[MailNotification]

CREATE PROCEDURE [dbo].[MailNotification]

@EmployeeId INT,

@EmployeeCode VARCHAR(25),

@EmailAddress VARCHAR(50),

@EmployeeName VARCHAR(25),

@TotalSalary FLOAT

BEGIN

SET NOCOUNT ON;

--Write logic/code to send Email using sp_send_dbmail

END

Methods for performing row by row operations are described below.

Using Cursor

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sendEmailEmployeeSalaryDetails]') AND type in (N'P', N'PC'))

DROP PROCEDURE [dbo].[sendEmailEmployeeSalaryDetails]

CREATE PROCEDURE [dbo].[sendEmailEmployeeSalaryDetails]

@Month INT

BEGIN

SET NOCOUNT ON;

DECLARE @EmployeeId INT

DECLARE @EmployeeCode VARCHAR(25)

DECLARE @EmailAddress VARCHAR(50)

DECLARE @EmployeeName VARCHAR(25)

DECLARE @TotalSalary FLOAT

DECLARE EmailNotification CURSOR FOR

SELECT EM.EmployeeId,EmployeeCode,EmailAddress,EmployeeName,TotalSalary FROM EmployeeMaster EM

INNER JOIN EmployeeSalaryDetails ES ON EM.EmployeeId = ES.EmployeeId

WHERE ES.Month = @Month

OPEN EmailNotification

FETCH NEXT FROM EmailNotification

INTO @EmployeeId,@EmployeeCode,@EmailAddress,@EmployeeName,@TotalSalary

WHILE @@FETCH_STATUS = 0

BEGIN

Print @EmployeeId

EXEC MailNotification @EmployeeId, @EmployeeCode ,@EmailAddress , @EmployeeName ,@TotalSalary

FETCH NEXT FROM EmailNotification

INTO @EmployeeId,@EmployeeCode,@EmailAddress,@EmployeeName,@TotalSalary

END

CLOSE EmailNotification

DEALLOCATE EmailNotification

END

Using While loop with Temp Table or Table Variable:

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sendEmailEmployeeSalaryDetails]') AND type in (N'P', N'PC'))

DROP PROCEDURE [dbo].[sendEmailEmployeeSalaryDetails]

CREATE PROCEDURE [dbo].[sendEmailEmployeeSalaryDetails]

@Month INT

BEGIN

SET NOCOUNT ON;

--WE CAN USE TABLE VARIBLE INSTED OF TEMP TABLE.

CREATE TABLE #TempTable

(

TempId INT IDENTITY(1,1),

EmployeeId INT,

EmployeeCode VARCHAR(25),

EmailAddress VARCHAR(50),

EmployeeName VARCHAR(25),

TotalSalary FLOAT

)

DECLARE @Count INT

DECLARE @i INT = 1

DECLARE @EmployeeId INT

DECLARE @EmployeeCode VARCHAR(25)

DECLARE @EmailAddress VARCHAR(50)

DECLARE @EmployeeName VARCHAR(25)

DECLARE @TotalSalary FLOAT

INSERT INTO #TempTable

SELECT EM.EmployeeId,EmployeeCode,EmailAddress,EmployeeName,TotalSalary FROM EmployeeMaster EM

INNER JOIN EmployeeSalaryDetails ES ON EM.EmployeeId = ES.EmployeeId

WHERE ES.Month = @Month

SELECT @Count = COUNT(1) FROM #TempTable

WHILE (@i <= @count)

BEGIN

SELECT @EmployeeId = EmployeeId, @EmployeeCode = EmployeeCode ,@EmailAddress = EmailAddress , @EmployeeName = EmployeeName ,@TotalSalary = TotalSalary

FROM #TempTable

WHERE TempId= @i

EXEC MailNotification @EmployeeId, @EmployeeCode ,@EmailAddress , @EmployeeName ,@TotalSalary

PRINT @EmployeeCode

SET @i += 1

END

END

Using COALSCE

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sendEmailEmployeeSalaryDetails]') AND type in (N'P', N'PC'))

DROP PROCEDURE [dbo].[sendEmailEmployeeSalaryDetails]

CREATE PROCEDURE [dbo].[sendEmailEmployeeSalaryDetails]

@Month INT

BEGIN

DECLARE @SQL VARCHAR(MAX)

SELECT @SQL=COALESCE(@SQL,'')+'EXEC MailNotification '+ CAST(EM.EmployeeId AS VARCHAR(10))+ ', ''' +

EmployeeCode + ''', ''' + EmailAddress + ''', ''' + EmployeeName + ''', ' + CAST(TotalSalary AS VARCHAR(25)) + '; '

FROM EmployeeMaster EM

INNER JOIN EmployeeSalaryDetails ES ON EM.EmployeeId = ES.EmployeeId

WHERE ES.Month = @Month

EXEC (@SQL)

END

This can also be done by SQL Server Integration Services (SSIS). SSIS also supports "for" and "foreach" loops. Use of these provides functionality like Cursors (row by row operations).

Conclusion

In this article I am trying to explain alternatives for Cursors. We shall use a Cursor when there is no other option than Cursor.