Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Row-By-Row Processing Without Cursor

By Amin Sobati, (first published: 2006/12/07)

INTRODUCTION

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 3 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 Books (
BookCode VARCHAR(5),
BookDesc VARCHAR(100))

-- The SP which processes new BookCode during insertion
CREATE PROC usp_Process
@BookCode VARCHAR(5)
AS
-- Do something useful with each BookCode. We simply print it
Print 'SP is processing... ' + @BookCode
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 BookCode:
CREATE TRIGGER tr1 ON Books AFTER INSERT AS
	DECLARE @sql VARCHAR(8000)
	SET @sql=''
	SELECT @sql=@sql+'EXEC usp_Process ''' + BookCode + '''; ' FROM Inserted
	PRINT 'Trigger is preparing Dynamic T-SQL: ' + @sql -- Just o see @sql
	EXEC (@sql)
GO	
Now let's try to see how it works:
INSERT Books
	SELECT 'A','Book desc 1' UNION
	SELECT 'B','Book desc 2' UNION
	SELECT 'C','Book desc 3'

Trigger is preparing Dynamic T-SQL: EXEC usp_Process 'A'; EXEC usp_Process 'B'; EXEC usp_Process 'C'; 
SP is processing... A
SP is processing... B
SP is processing... C

(3 row(s) affected)
This manner can be used even if you need to send more parameters to the SP. 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!

Total article views: 44517 | Views in the last 30 days: 79
 
Related Articles
FORUM

Doubt About Insert Trigger?

Doubt About Insert Trigger?

FORUM

Cursor

cursor

FORUM

Bulk Insert and Triggers

Bulk Insert and Triggers

FORUM

not updating inserted row in an insert trigger

not updating inserted row in an insert trigger

FORUM

about triggers and bulk insert

about triggers and bulk insert

Tags
triggers    
t-sql    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones