Questions

Execute Stored Procedure in a Resultset

Tags:
+
0 Votes
Locked

Execute Stored Procedure in a Resultset

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
  • +
    0 Votes
    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.

    +
    0 Votes

    Hi

    aloksaxena01

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

  • +
    0 Votes
    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.

    +
    0 Votes

    Hi

    aloksaxena01

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