T-SQL: Looping through an array of known values

Question

Here's my scenario:

Let's say I have a stored procedure in which I need to call another stored procedure on a set of specific ids; is there a way to do this?

i.e. instead of needing to do this:

exec p_MyInnerProcedure 4 exec p_MyInnerProcedure 7 exec p_MyInnerProcedure 12 exec p_MyInnerProcedure 22 exec p_MyInnerProcedure 19 

Doing something like this:

*magic where I specify my list contains 4,7,12,22,19*  DECLARE my_cursor CURSOR FAST_FORWARD FOR *magic select*  OPEN my_cursor  FETCH NEXT FROM my_cursor INTO @MyId WHILE @@FETCH_STATUS = 0 BEGIN  exec p_MyInnerProcedure @MyId  FETCH NEXT FROM my_cursor INTO @MyId END 

My Main goal here is simply maintainability (easy to remove/add id's as the business changes), being able to list out all Id's on a single line... Performance shouldn't be as big of an issue

Answer 1

declare @ids table(idx int identity(1,1), id int)  insert into @ids (id)     select 4 union     select 7 union     select 12 union     select 22 union     select 19  declare @i int declare @cnt int  select @i = min(idx) - 1, @cnt = max(idx) from @ids  while @i < @cnt begin      select @i = @i + 1       declare @id = select id from @ids where idx = @i       exec p_MyInnerProcedure @id end