Run a stored procedure for every row in a table

Symptoms

You want to run a stored procedure, passing in the ID (or other value) for every row in an existing table

Cause

n/a

Resolution

The following code illustrates how you might run a stored procedure many times, passing in a value which has been selected from each row in a table. -- From an example table CREATE TABLE MyTable ( Id int, AnotherValue VARCHAR(255) );

-- Populate your table with some records

-- Declare & init (2008 syntax) DECLARE @ThisId INT = 0 DECLARE @ThisValue VARCHAR(255) = null

-- Iterate over all rows WHILE (1 = 1) BEGIN

-- Get next Id SELECT TOP 1 @ThisId = Id, @ThisValue = AnotherValue FROM MyTable WHERE Id > @ThisId ORDER BY Id

-- Exit loop if no more rows IF @@ROWCOUNT = 0 BREAK;

-- call your sproc EXEC dbo.YOURSPROC @ThisId @ThisValue

END