Post reply

store procedure over a list

  • October 7, 2011 at 2:30 pm

    #249640

    Hello,

    I have a stored Procedure which takes an integer as a parameter. I would like to execute that stored procedure over a list of integer while executing. I do not want to make any change to the procedure. Is there a way like executing the procedure using "in" or something like:

    exec procThis in (1,2,3,... )

    Thanks

  • October 7, 2011 at 2:40 pm

    #1391977

    create another procedure that can execute the proc in question via a loop.

    You can allow this new proc to accept the delimited list and then parse that list into rows via a string splitter - then call the original proc for each parameter in that list.

    Another option is to rewrite it so it will function as you desire.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • October 7, 2011 at 2:44 pm

    #1391982

    As far as I'm aware a cursor is the only way to do this.

    --Create #temp table and insert numbers

    DECLARE int_cursor CURSOR

    FOR SELECT intVal FROM #temp

    OPEN int_cursor

    DECLARE @currInt INT

    FETCH NEXT FROM int_cursor INTO @currInt

    WHILE @@FETCH_STATUS = 0

    BEGIN

    --Your code here (using the @currInt variable)

    END

    CLOSE db_cursor

    DEALLOCATE db_cursor

    Cursors tend to be pretty slow though, so as a rule I would try avoiding them. Without seeing what you are trying to do though it is hard to suggest an alternative.

    Hope that helps!

  • October 7, 2011 at 4:05 pm

    #1391998

    Thanks, this is what I needed.

  • October 8, 2011 at 10:42 am

    #1392079

    seftest_09 (10/7/2011)

    Thanks, this is what I needed.

    I'm not so sure that's true. Depending on what the stored procedure actually does, it may be VERY beneficial to performance and resource usage to rewrite the procedure to operate in a Set-Based fashion instead of RBAR.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

Viewing 5 posts - 1 through 4 (of 4 total)

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