Executing a SQL Query multiple times with different parameters

Question

I have a SQL stored procedure that I need to execute several times with different parameters. Is it possible to execute a SQL-script of some kind that will execute multiple times with like an array or other data-structure of different parameters? Any thoughts on this?

It is possible to loop through the values of a column in a table and use them as parameters in a stored procedure call. But, that could be slow. Sometimes it is better (if it's even possible) to rewrite the stored procedure as non-procedural SQL. Of course, you haven't shown your code, so it is impossible to say what's best in your case. — John Pick, Mar 05 '12 at 20:27

score 5 · Accepted Answer · edited May 23 '17 at 11:48

5

you can use a cursor (but if it's possible to restructure your code, try YS's answer):

EDIT: added FAST_FORWARD as per @YS's suggestion

DECLARE @param INT

-- getting your parameter from the table
DECLARE curs CURSOR LOCAL FAST_FORWARD FOR
    SELECT afield FROM atable WHERE ...

OPEN curs

FETCH NEXT FROM curs INTO @param

-- executing your stored procedure once for every value of your parameter     
WHILE @@FETCH_STATUS = 0 BEGIN
    EXEC usp_stored_Procedure @param
    FETCH NEXT FROM curs INTO @param
END

CLOSE curs
DEALLOCATE curs

edited May 23 '17 at 11:48

Community

1
1

answered Mar 05 '12 at 20:25

Paolo Falabella

24,914
3
72
86

4

Consider using `FAST_FORWARD` for added performance if cursor is read only & you're only moving forward. ie: `DECLARE curs CURSOR LOCAL FAST_FORWARD FOR...` – YS. Mar 05 '12 at 20:29

score 5 · Answer 2 · answered Mar 05 '12 at 20:32

I'd probably restructure the design a bit to fit the need (to workaround the cursor) - ie:

insert values to be executed in stored proc into a temp table, then call the stored proc (the stored proc will then read the temp table)
call stored proc using table-valued parameter

Executing a SQL Query multiple times with different parameters

2 Answers2