Post reply

Running an SP inside a select statement

  • December 27, 2006 at 10:13 am

    #103290

    Howdy all,

    in a new stored procedure, i want to so a 'select ID from table' to get all of the IDs in the table, and then run an existing SP for each ID. Seems simple enough, but then so am I.

    Thanks

  • December 27, 2006 at 11:40 am

    #680154

    Use the corsor or while loop to do this or

    you can use undocumented extended procedure xp_execresultset procedure to do this...

     

    MohammedU
    Microsoft SQL Server MVP

  • December 27, 2006 at 12:54 pm

    #680162

    i'll give it a shot

    thanks

  • December 28, 2006 at 7:13 am

    #680248

    Why can't you rewrite the said sp to process more than one line at the same time??  Would be much simpler to do that way.

  • December 28, 2006 at 8:11 am

    #680260

    Re-write the called sp as a function, then modify the select statement to

    SELECT ID, dbo.fn_YourFunction(ID) ...

  • December 28, 2006 at 9:02 am

    #680272

    Potato, potâto.  That will still do row by row processing which is exactly the opposite of what sql server was optimisez for.

     

    What is so complexe in that procedure that sql server can't handle more than one row at the time? >>  Never seen that happen, it's usually our own limits or a business requirement (extremely rare).

  • December 28, 2006 at 9:17 am

    #680282

    it was more the limits of my sql knowledge. went a fully different route. thanks all

  • December 28, 2006 at 9:42 am

    #680301

    Can you post the final solution so that others may benefit from your work.

     

    TIA.

  • December 28, 2006 at 10:55 am

    #680320

    it wasn't really a solution per say, i just realised that looking at the problem in a different way eliminated it

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply