[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

 

35 Comments

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

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

  • 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.

  • Very handy!

    EXECUTE sp_executesql @statement

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

    Many thanks!

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

  • 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..

  • 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?).

  • 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

  • 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.

  • 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.

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

    Thanks

  • Works Great, Thanks

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

  • very useful creative work, thanks

  • 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

  • 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

  • Thanks a lot! It helped me.

  • good very good
    and in 2005 you can use Row_Number()

  • 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

  • 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 ;)

  • This is nice. But my requirement is different. I neet to display the employeeid, from XXX.

    is it possible

  • Thanks man, this was realy helpful

  • Thanks! Exactly what I was looking for.

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

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

  • This was a good example.

  • 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

  • This code was very usefull to me!!!
    Thanks!

  • 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!

  • 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.

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

  • Very informative.
    Thanks.

  • Very goooood

    Thanks

  • Excellent!
    Thanks

  • Very Helpful. Thanks.

Comments have been disabled for this content.