Search code examples
sql-serversql-scripts

Executing a SQL Query multiple times with different parameters

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?

Solution

  • 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