Question

Locked

Execute Stored Procedure in a Resultset

By Congero ·
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

This conversation is currently closed to new comments.

3 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Answers

Collapse -

In T-SQL

by Tony Hopkinson In reply to Execute Stored Procedure ...

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.

Collapse -

Hi

by aloksaxena01 In reply to Execute Stored Procedure ...

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

Back to Networks Forum
3 total posts (Page 1 of 1)  

Hardware Forums