[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