- In: Uncategorized
- Comments Off
So I’ve fallen far short of posting often.
To remedy this, I’m just going to start posting little programming snippets:
To remedy this, I’m just going to start posting little programming snippets:
So you want to feed the results of SQL query into a stored procedure (SPROC)? Well it’s easy!
Let’s say you have the following SQL query returning a column of ID as ints:
SELECT ID FROM MyTable
Now for each result ID (int) you want to execute a stored procedure; it’s simple, just use a cursor.
The comments should make what is going on relative clear.
-- a temp variable DECLARE @_temp INT -- declare a cursor DECLARE c1 CURSOR FOR SELECT ID FROM MyTable -- your query -- open the cursor OPEN c1 -- Put the first row (ID) resulting from your query into @temp FETCH NEXT FROM c1 INTO @_temp -- DO WHILE there are rows in the result set from your query WHILE @@FETCH_STATUS = 0 BEGIN EXEC [MySPROC] @inputId = @_temp
--Execute the SPROC with the current row
FETCH NEXT FROM c1
--Fetch the next row rom your query into the temp variable
INTO @_temp END -- Close and deallocate the cursor CLOSE c1 DEALLOCATE c1
Is it that easy? Yes, it’s that easy!
