Run a stored procedure for every row in a table

Created by Jeremy Burgess, Modified on Wed, 6 Jul, 2022 at 1:15 PM by Jeremy Burgess

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