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?
-
Where do the parameters come from? A DB table?John Pick– John Pick2012-03-05 20:22:04 +00:00Commented Mar 5, 2012 at 20:22
-
yep. A particular column in a table no less.SoftwareSavant– SoftwareSavant2012-03-05 20:23:17 +00:00Commented Mar 5, 2012 at 20:23
-
1It 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– John Pick2012-03-05 20:27:36 +00:00Commented Mar 5, 2012 at 20:27
Add a comment
|
2 Answers
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
1 Comment
YS.
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...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
