Skip to main content

Avoid Cursor In row by row processessing

Cursors exist because there are situations that row-by-row processing is inevitable. However as they are resource intensive, developers always try to refine their codes and bypass cursors using T-SQL tricks.

Not all scenarios are capable for this replacement, but I have seen many of them! One of the common usages of cursors is inside triggers for processing the Deleted and Inserted tables. At the time of writing this article, SQL Server does not support any mechanism to fire a trigger for each row separately when a DML statement affects more than one row.

As an example, an Insert command is inserting 5 records (from a query) into a table. Your trigger needs to retrieve each PK value from Inserted table and send it to a particular Stored Procedure for some processes. Iterating through a cursor made of PKs might seem the first option. However in this case we can use the power of variables that live within a query, then Dynamic T-SQL Execution finalizes the trick! Let's create necessary objects:

CREATE TABLE [dbo].[Employee](
[EmployeeID] [int] IDENTITY(1,1) NOT NULL,
[EmployeeName] [nvarchar](50) COLLATE Latin1_General_CI_AI NOT NULL,
CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED
(
[EmployeeID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]// Create Procedure...!
CREATE PROCEDURE [dbo].[usp_Process]
-- Add the parameters for the stored procedure here
@EmpName varchar(50)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

Print 'Sp is processing..' + @EmpName

END
GO

All we need to do in the trigger is to construct a string of T-SQL commands that contain EXEC usp_Process for each EmpName:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

Create TRIGGER [tr1] ON [dbo].[Employee] AFTER INSERT AS
DECLARE @sql VARCHAR(8000)
SET @sql=''
SELECT @sql=@sql+'EXEC usp_Process ''' + EmployeeName + '''; ' FROM Inserted
PRINT 'Trigger is preparing Dynamic T-SQL: ' + @sql
EXEC (@sql)// Add Some Entries
Insert Employee(EmployeeName)
Select 'Bahutik' Union
Select 'Manan' Union
Select 'Tarang' Union
Select 'Kuldeep' Union
Select 'Kejal' Union
Select 'Kunal'

Ouput Message:
Trigger is preparing Dynamic T-SQL: EXEC usp_Process 'Tarang'; EXEC usp_Process 'Manan'; EXEC usp_Process 'Kunal'; EXEC usp_Process 'Kuldeep'; EXEC usp_Process 'Kejal'; EXEC usp_Process 'Bahutik';
Sp is processing..Tarang
Sp is processing..Manan
Sp is processing..Kunal
Sp is processing..Kuldeep
Sp is processing..Kejal
Sp is processing..Bahutik

(6 row(s) affected)

This manner can be used even if you need to send more parameters to the SP.
Note: Please be careful with @sql here because your dynamically-built string cannot exceed 8000 characters. Fortunately SQL Server 2005 developers can benefit from MAX length of VARCHAR and NVARCHAR data types!

Conclusion:
Use the power of T-SQL and SELECT statement whenever you can. They are flexible enough to help us to perform some sort of row-by-row processing faster without bothering the hardware. Experts think about a Cursor as the last option!

Comments

Popular posts from this blog

Custom Effective Paging With Sorting across all pages GridView in Asp.Net

Hi, I need to comeback to blogging anyhow, since it was too long break. Hence yesterday  i decided to start blogging again, and thought of this topic.  When Microsoft shipped GridView control with Asp.net, it came with lot of new functionality, but it also lacking of existing functionality  like 'VirtualCount' property  which was available with DataGrid but not with GridView. Since because of this it is very much difficult to implement Custom Paging with Grid View.  Now what is custom paging and default paging?  To answer this question i will take an example. I need to display records on a page in tabular format, with paging and sorting.  Well if you go with default paging and default sorting your development work is easy. But with it there is a drawback. - It is grid view control which decides what records to display and sorted by which column. So for that it need complete set of all records. Suppose if you want to display only 25 records, a...

Sql Server Tips and Guidelines that Developer Must Know

Introduction The following Tips and Guidelines are very important from the performance perspective of your SQL Server development or production database. The best way to achieve optimum performance benefits is to experiment with the following guidelines while using/viewing a query execution plan, both with and without implementing the respective tip/guideline. Tips and Guidelines • As a common practice, every table should have a clustered index. Generally, but not always, the clustered index should be on a column that monotonically increases, such as an identity column or some other column where the value is unique. In many cases, the primary key is the ideal column for a clustered index. • Indexes should be measured on all columns that are frequently used in WHERE , ORDER BY , GROUP BY , TOP and DISTINCT clauses. • Do not automatically add indexes on a table because it seems like the right thing to do. Only add indexes if you know that they will be used by the querie...

Learn How to Extend Visual Studio

Millions of developers in both small and large enterprises use Visual Studio to more easily use the latest platform technology to revolutionize the way they build, maintain, and deploy today’s complex business applications. Visual Studio supports a rich, diverse ecosystem of developers who ship thousands of Visual Studio integrated products to make it easy for Visual Studio users to use their favorite tool their way. And if someone else hasn’t created it, you can -- the Visual Studio SDK is freely available to the developer community and contains everything you need to extend Visual Studio. With Visual Studio, you’ll find new opportunities to build extensions for specific development needs. These include the Visual Studio Shell, a foundational IDE for custom tools and programming languages, Domain-Specific Language (DSL) tools, a set of tools to help you create graphical models that generate code, and support for Visual Basic within the Visual Studio SDK. Released on a quarterly basis,...