If you want to execute a stored procedure for each row in T-SQL 2012, where the Id column is not a consecutive number, you van use the following T-SQL code

 

SQL Server >= 2012

declare @Person table
(
    Id          int                not null,
    Name        varchar(max)    not null
)

insert into @Person values
    (1, 'John'),
    (4, 'Mike')


-- Determine loop boundaries.
declare @Id int = 0
declare @counter int = 0
declare @total int = isnull((select count(1) from @Person), 0)
    
-- Iterate records.
while (@counter <> (@total))
begin  
        -- record to proces.
        set @Id = (select Id from @Person order by Id offset @counter rows fetch next 1 rows only)

        -- Exec sproc here:
        select @Id
                    
        -- Increase counter to break the loop after all records are processed.
        set @counter = @counter + 1
end

If however you are stuck on SQL Server before 2012 and your Id’s are consecutive you can use the following code:

 

SQL Server < 2012

 

declare @Person table
(
    Id            int                not null identity(1,1),
    Name        varchar(max)    not null
)

insert into @Person values 
    ('John'),
    ('Mike')

-- Determine first record to process.
declare @Id int = (select min(Id) from @Person)

-- Iterate records.
while (@Id is not null) 
begin  
    
    -- Exec sproc here:
    select    Name
    from    @Person
    where    Id = @Id
            
    -- Select next record, this will break the loop if next record is not found.
    set @Id = (select Id from @Person where Id = @Id + 1)
end

2 Comments

  1. Hej, I just came around your scripts, and I have a question…What if you’re stuck on SQL Server < 2012 (i.e. SQL Server 2008) and my Id's are not consecutive??

    Thank you.

    Jordi
  2. I have a query regarding performance of running multiple Execute SP statements written together versus executing the SP inside a loop. Which will have better performance? I have to run SP for almost 2000 different parameters.

    Ankita Bikrol

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.