- Follow via:
- RSS
- Email Alert
Question
0
Votes
Execute Stored Procedure in a Resultset
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
21st Feb 2007
Answers (2)
0
Votes
In T-SQL
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.
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.
Updated - 21st Feb 2007
0
Votes
Hi
u did not specify the u query reltated to web application or desktopapplication and version u used SQL Server & net
22nd Feb 2007
