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

User · Answer

use a static cursor variable and a split function:

declare @comma_delimited_list varchar(4000)
set @comma_delimited_list = '4,7,12,22,19'

declare @cursor cursor
set @cursor = cursor static for 
  select convert(int, Value) as Id from dbo.Split(@comma_delimited_list) a

declare @id int
open @cursor
while 1=1 begin
  fetch next from @cursor into @id
  if @@fetch_status <> 0 break
  ....do something....
end
-- not strictly necessary w/ cursor variables since they will go out of scope like a normal var
close @cursor
deallocate @cursor

Cursors have a bad rep since the default options when declared against user tables can generate a lot of overhead.

But in this case the overhead is quite minimal, less than any other methods here. STATIC tells SQL Server to materialize the results in tempdb and then iterate over that. For small lists like this, it's the optimal solution.

User · Answer

What I do in this scenario is create a table variable to hold the Ids     Declare  Ids Table  id integer primary Key not null    Insert  Ids id  values  4   7   12   22   19    --  or call another table valued function to generate this table   Then loop based on the rows in this table    Declare  Id Integer   While exists  Select   From  Ids      Begin       Select  Id   Min id  from  Ids       exec p MyInnerProcedure  Id        Delete from  Ids Where id    Id     End   or       Declare  Id Integer   0 -- assuming all Ids are  gt  0   While exists  Select   From  Ids                 where id  gt   Id      Begin       Select  Id   Min id         from  Ids Where id  gt   Id       exec p MyInnerProcedure  Id      End   Either of above approaches is much faster than a cursor  declared against regular User Table s     Table-valued variables have a bad rep because when used improperly   for very wide tables with large number of rows  they are not performant   But if you are using them only to hold a key value or a 4 byte integer  with a index  as in this case  they are extremely fast

User · Answer

CREATE TABLE  ListOfIDs  IDValue INT   DECLARE  IDs VARCHAR 50    ID VARCHAR 5  SET  IDs    OriginalListOfIDs        WHILE LEN  IDs   gt  1 BEGIN SET  ID   SUBSTRING  IDs  0  CHARINDEX       IDs    INSERT INTO  ListOfIDs  IDValue  VALUES  ID   SET  IDs   REPLACE        IDs         ID            END  SELECT    FROM  ListOfIDs

User · Answer

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  lt   cnt begin      select  i    i   1       declare  id   select id from  ids where idx    i       exec p MyInnerProcedure  id end

User · Answer

You can try as below    declare  list varchar MAX    i int select  i 0   list   4 7 12 22 19    while   i  lt  LEN  list   begin     declare  item varchar MAX      SELECT   item   SUBSTRING  list    i CHARINDEX      list  i - i      select  item       --do your stuff here with  item       exec p MyInnerProcedure  item       set  i   CHARINDEX      list  i  1     if  i   0  set  i   LEN  list   end

User · Answer

Make a connection to your DB using a procedural programming language  here Python   and do the loop there  This way you can do complicated loops as well     make a connection to your db import pyodbc conn   pyodbc connect                             Driver  ODBC Driver 13 for SQL Server                           Server serverName                          Database DBname                          UID userName                          PWD password                             cursor   conn cursor      run sql code for id in  4  7  12  22  19     cursor execute         exec p MyInnerProcedure          format id

User · Answer

I usually use the following approach  DECLARE  calls TABLE       id INT IDENTITY 1 1       parameter INT        INSERT INTO  calls select parameter from some table where some condition -- here you populate your parameters  declare  i int declare  n int declare  myId int select  i   min id    n   max id  from  calls while  i  lt    n begin     select           myId   parameter     from           calls     where id    i          EXECUTE p MyInnerProcedure  myId     set  i    i 1 end

[sql] T-SQL: Looping through an array of known values

Examples related to sql

Examples related to sql-server

Examples related to tsql