[T-SQL] Call a stored procedure once for each row in a query or table

Although it's not at all good from a database performance perspective, sometimes data import or upgrade scenarios require a script which calls a stored procedure once for each row in a table. In this example, I'm calling usp_InsertUser on every employee in EmployeeImportTable.

USE [DBNAME]
GO

declare @Proc nvarchar(50)
declare @RowCnt int
declare @MaxRows int
declare @ExecSql nvarchar(255)

select @RowCnt = 1
select @Proc = 'usp_InsertUser'

-- These next two rows are specific to source table or query
declare @Import table (rownum int IDENTITY (1, 1) Primary key NOT NULL , EmployeeID varchar(9))
insert into @Import (EmployeeID) select EmployeeID from EmployeeImportTable

select @MaxRows=count(*) from @Import

while @RowCnt <= @MaxRows
begin
    select @ExecSql = 'exec ' + @Proc + ' ''' + EmployeeID + '''' from @Import where rownum = @RowCnt 
    --print @ExecSql
    execute sp_executesql @ExecSql
    Select @RowCnt = @RowCnt + 1
end

 

Published Wednesday, April 12, 2006 5:28 AM by Jon Galloway
Filed under:

Comments

# re: [T-SQL] Call a stored procedure once for each row in a query or table

Why don't you using a local fast_forward read_only cursor?

Wednesday, April 12, 2006 7:37 AM by Hugo

# re: [T-SQL] Call a stored procedure once for each row in a query or table

I agree, the cursor option is what I use all the time.

Wednesday, April 12, 2006 9:04 AM by Liam Ponder

# re: [T-SQL] Call a stored procedure once for each row in a query or table

Hugo, Liam - I agree, a cursor would work here, and this is one of the very few times a cursor might make sense. I haven't tested, but I don't think it would necessarily perform any better.

I've made it a general policy not to use cursors because in almost every case they're the wrong solution to set based SQL operations.

Wednesday, April 12, 2006 12:26 PM by Jon Galloway

# [SQL] Some of my favorite INFORMATION_SCHEMA utility queries

Phil just posted about using INFORMATION_SCHEMA to bulletproof your SQL change scripts. I've been working

Friday, July 07, 2006 3:23 AM by JonGalloway.ToString()

# re: [T-SQL] Call a stored procedure once for each row in a query or table

Very handy!

EXECUTE sp_executesql @statement

Been trying to find out how you can run dynamic SQL and at last, the answer.

Many thanks!

Friday, February 23, 2007 6:57 AM by Sean

# re: [T-SQL] Call a stored procedure once for each row in a query or table

Thanks a lot, this example helps me to run a trigger for each update in an Update FROM Select Scenario

Thursday, March 15, 2007 3:14 PM by Victor Gil

# re: [T-SQL] Call a stored procedure once for each row in a query or table

Thanks Jon,

IMHO, this is much more elegant than a cursor.

I agree with your statement in regard to cursors also. In my experience there aren't many times that you cant achieve what you need in a "for each row" situation that you cant solve with a UDF, Dynamic SQL and handy tricks like this.

Great work..

Thursday, July 12, 2007 5:36 PM by Damen

# re: [T-SQL] Call a stored procedure once for each row in a query or table

Thanks.

I needed to do a similar thing, importing contacts from Exchange and having to run a insertContact sp for each contact (the sp was already written and is quite complex).

I knocked up a SSIS package that loads the records into a recordset destination and then uses a foreach loop container to execute the stored proc with the correct parameters. I did it mostly to learn, but it's amazing what you can do with SSIS these days.

I think the most elegant way would be to unravel the sp so that the whole thing is set based, but this risks breaking something that I know works (TDD for databases anyone?).

Monday, October 01, 2007 4:16 AM by Andy

# re: [T-SQL] Call a stored procedure once for each row in a query or table

Thanks.

Friday, November 30, 2007 4:50 PM by Al

# re: [T-SQL] Call a stored procedure once for each row in a query or table

For this example, why do you need dynamic SQL? If you've hardcoded the @proc variable, there's no reason not to just do

declare @emp_id int

while @RowCnt <= @MaxRows

begin

   select @emp_id=EmployeeID from @Import where rownum = @RowCnt

   execute usp_InsertUser @emp_id

   Set @RowCnt = @RowCnt + 1

end

Tuesday, December 11, 2007 6:27 PM by sam

# re: [T-SQL] Call a stored procedure once for each row in a query or table

But is this really set based since your loop continually selects one row from the temporary @Import table variable? It's finding a one row set at a time. On a large table, continually querying the table can take a long time since each query will cause a table scan.

Wednesday, January 16, 2008 7:32 PM by Ed

# SQL de cursor kullanmadan tablodaki veriyi sat??r sat??r okuma | ??K?? DAK??KA G??NCEL HABER MERKEZ??

Pingback from  SQL de cursor kullanmadan tablodaki veriyi sat??r sat??r okuma | ??K?? DAK??KA G??NCEL HABER MERKEZ??

Monday, February 04, 2008 7:09 AM by SQL de cursor kullanmadan tablodaki veriyi sat??r sat??r okuma | ??K?? DAK??KA G??NCEL HABER MERKEZ??

# re: [T-SQL] Call a stored procedure once for each row in a query or table

Only thing missing in "select @ExecSql = 'exec ' + @Proc + ' ''' + EmployeeID + '''' from @Import where rownum = @RowCnt " is to use "CAST(EmployeeID AS NVARCHAR)", otherwise this will result in an error while using a numeric ID.

Wednesday, March 12, 2008 1:27 PM by ppiglets

# re: [T-SQL] Call a stored procedure once for each row in a query or table

good job.  thanks very much

Tuesday, April 08, 2008 8:44 AM by uncle dunc

# re: [T-SQL] Call a stored procedure once for each row in a query or table

Good One, Always wanted to use something else apart from Cursors.

Thanks

Friday, May 09, 2008 9:06 AM by Saravan

# re: [T-SQL] Call a stored procedure once for each row in a query or table

Works Great, Thanks

Wednesday, May 28, 2008 11:20 AM by Oscar

# re: [T-SQL] Call a stored procedure once for each row in a query or table

Thanks for that. I've just used something similar to this over a linked server using OPENQUERY. Worked like a charm. Thanks again.

Monday, June 02, 2008 9:29 AM by Alex

# re: [T-SQL] Call a stored procedure once for each row in a query or table

very useful creative work, thanks

Saturday, July 26, 2008 2:07 PM by don raj

# t-sql stored procedure call for every row in a table &laquo; Ashish Lakhiani&#8217;s Weblog

Pingback from  t-sql stored procedure call for every row in a table &laquo; Ashish Lakhiani&#8217;s Weblog

Tuesday, September 30, 2008 11:34 PM by t-sql stored procedure call for every row in a table « Ashish Lakhiani’s Weblog

# re: [T-SQL] Call a stored procedure once for each row in a query or table

Hey I need a stored proc to check the location of a particular field that is passed as a parameter, the output should be like this :

Tablename, columnname, no of occurences of the field.

Your help is appreciated

Thanks

Wednesday, October 01, 2008 5:17 PM by Mark

# re: [T-SQL] Call a stored procedure once for each row in a query or table

I'm getting the following :

A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.

I have several other parameters that I need to pass to the SP

Monday, November 03, 2008 12:57 PM by dotScott

# re: [T-SQL] Call a stored procedure once for each row in a query or table

Thanks a lot! It helped me.

Wednesday, February 25, 2009 2:17 PM by Ravi

# re: [T-SQL] Call a stored procedure once for each row in a query or table

good very good

and in 2005 you can use Row_Number()

Tuesday, May 12, 2009 12:17 PM by zmohamed

# re: [T-SQL] Call a stored procedure once for each row in a query or table

definitely works, but i'd argue a cursor is a much better solution since not only are you going to execute a stored proc for each record, but you are also issuing a select statement for each record

Wednesday, May 13, 2009 1:24 AM by peter

# How to protect your Community Server site from spammers

One should always be aware that your site could be attacked by spam bots if you don’t take the proper

Wednesday, May 27, 2009 8:45 AM by Blake Niemyjski

# re: [T-SQL] Call a stored procedure once for each row in a query or table

Congratulations on re-implementing the cursor!. Because this is definitly the RBAR pattern. It's not as set based as u think.

Use the fast_forward read_only cursor instead and u will get better performance.

So Jon , You violated your policy regarding cursors and did a much worse job doing so ;)

Thursday, June 11, 2009 9:55 AM by Kim

# re: [T-SQL] Call a stored procedure once for each row in a query or table

This is nice. But my requirement is different. I neet to display the employeeid,<exec procedure_name emplyeeid,null> from XXX.

is it possible

Monday, July 06, 2009 4:21 AM by Vijay

# [T-SQL] Call a stored procedure once for each row in a query or table

You've been kicked (a good thing) - Trackback from DotNetKicks.com

Wednesday, July 29, 2009 10:58 AM by DotNetKicks.com

# re: [T-SQL] Call a stored procedure once for each row in a query or table

You can create a dummy field on the table and  write the logic of the sp into an update trigger then update the rows for which  you want the sp logic to execute.

Friday, September 25, 2009 12:30 AM by VS

Leave a Comment

(required) 
(required) 
(optional)
(required)