T-SQL: Looping through an array of known values

John_30006 0
Viewed 207123

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

tsql sql-server sql
7 Answers

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_24452730
Related