Question Locked

Execute Stored Procedure in a Resultset

0 helpful
3 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
I have a sql server resultset that returns productid numbers. I would like to loop thru this resultset and run a stored procedure using the productid as the input parameter. Any samples would be helpful. thanks

3 replies

by tony hopkinson

some thing like

Declare csr_Products cursor For (insert your query here)
Declare @Product_ID int
open csr_Products
Fetch From csr_Products into @Product_ID
While @@Fetch_Status = 0
Begin
Exec myProdSP @Product_ID
Fetch Next From csr_Products into @Product_ID
end
close csr_Products
deallocate csr_Products

NB optimisation for cursors is usually poor, definitely is in SQL Server, so if you are going to make frequent use of this technique be prepared for some performance losses and extra locking issues. For one off, admin and out of hours type stuff though, it's a goer.

You can add some modifiers when you declare the cursor such as forward only read ... which may be worth the effort for a bit more performance.

by aloksaxena01

u did not specify the u query reltated to web application or desktopapplication and version u used SQL Server & net

This discussion is closed to new replies.