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]
GO
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]
GO
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]
GO
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]
GO
ALTER TABLE [dbo].[EmployeeSalaryDetails] WITH CHECK ADD CONSTRAINT [FK_EmployeeSalaryDetails_EmployeeMaster] FOREIGN KEY([EmployeeId])
REFERENCES [dbo].[EmployeeMaster] ([EmployeeId])
GO
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]
GO
GO
CREATE PROCEDURE [dbo].[MailNotification]
@EmployeeId INT,
@EmployeeCode VARCHAR(25),
@EmailAddress VARCHAR(50),
@EmployeeName VARCHAR(25),
@TotalSalary FLOAT
AS
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
GO
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]
GO
CREATE PROCEDURE [dbo].[sendEmailEmployeeSalaryDetails]
@Month INT
AS
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]
GO
CREATE PROCEDURE [dbo].[sendEmailEmployeeSalaryDetails]
@Month INT
AS
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]
GO
CREATE PROCEDURE [dbo].[sendEmailEmployeeSalaryDetails]
@Month INT
AS
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.