by
back

mssql execute stored procedures in a loop

If you need to use a loop to execute a stored procedure in mssql on each row of a table, you can use a cursor:

declare @var1 int
declare @var2 int
declare cur CURSOR LOCAL for select field1, field2 from tablename where someotherfield is null

open cur

fetch next from cur into @var1, @var2

while @@FETCH_STATUS = 0 BEGIN
  exec your_stored_procedure @var1, @var2
  fetch next from cur into @var1, @var2
END

close cur
deallocate cur

Another solution is to create a temp-table in your stored procedure. You then load your data in this temp table and call a child stored procedure an that temp table.



comments powered by Disqus