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

[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 (11Primary key NOT NULL , EmployeeID varchar(9))
insert into @Import (EmployeeIDselect 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

# 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 1, 2007 4:16 AM by Andy

# 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

# 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 One, Always wanted to use something else apart from Cursors.

Thanks

Friday, May 9, 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 2, 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

# 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 1, 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 3, 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

# 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 6, 2009 4:21 AM by Vijay

# 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

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

Thanks man, this was realy helpful

Tuesday, January 26, 2010 10:05 AM by Tsvetozar Ivanov

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

Thanks!  Exactly what I was looking for.

Thursday, February 4, 2010 2:47 PM by Max Payne

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

Thanks man.  I use it to send an e-mail to each row older than a certain date.

Saturday, February 20, 2010 3:35 PM by jerry o

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

thanks, I was looking for somethin to loop over subquery results, this code seems helpful.

thanks

waleed

Monday, July 12, 2010 8:19 AM by waleed_m_m

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

This was a good example.

Monday, September 13, 2010 5:53 AM by Pramod

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

Hi :-)

In Oracle you can do something like:

select MyID, MyDatum1, MyDatum2, MyPack.MyStored( MyID ) as MyDatum3 from MyTable

Can you do something like this in TSQL as well?

Andrew

Saturday, September 18, 2010 6:29 AM by DeerBear

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

This code was very usefull to me!!!

Thanks!

Tuesday, October 5, 2010 11:02 AM by Tim Heemskerk

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

having problems executing stored procedures inside another stored proc.  its giving an error message:

Cannot add rows to sys.sql_dependencies for the stored procedure because it depends on the missing table 'dbo.myStoredProc'. The stored procedure will still be created; however, it cannot be successfully executed until the table exists.

however 'dbo.myStoredProc' is not a table its a stored procedure... weird, any ideas? thanks!

Wednesday, November 3, 2010 12:59 AM by nuk bottles

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

I stopped using cursors long time ago, at that time I made performance comparision tests using cursors (in diverse modalities) vs loops (like in this example) and I got better performance using loops. Cursors are more code-friendly but loops have better performance.

Sunday, April 17, 2011 2:00 PM by Victor Ayala

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

How to implement the same concept for a procedre with output parameter?

Tuesday, April 19, 2011 3:12 AM by Raj

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

Very informative.

Thanks.

Wednesday, April 20, 2011 10:20 AM by lycog

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

Very goooood

Thanks

Wednesday, June 6, 2012 12:19 PM by Serch

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

Excellent!

Thanks

Friday, July 27, 2012 12:02 PM by Mehmet

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

Very Helpful.  Thanks.

Wednesday, May 29, 2013 3:29 PM by Chris