Wrapper to run stored procedure many times

  • I need some guidance on what is a good construct/method of running a stored procedure many times by iterating through a temp table for it's imput parameters?

    Any help is appreciated.

    Thanks

    A clever person solves a problem. A wise person avoids it. ~ Einstein
    select cast (0x5365616E204465596F756E67 as varchar(128))

  • If the Temp Table has sequential numbering like that offered by an IDENTITY column and you've done nothing to disturb the sequence of numbers, then you could use a counter and WHILE loop to read each row into a variable and then exec the proc with the variable as a parameter to the proc.

    You could also use a cursor to do the same thing whether you have sequential numbering or not but you need to populate the cursor in the order that you wish to execute in.

    Last but not least, you could build a wad of concatenated SQL from your table to build all of the Execute commands as a single variable and then execute the variable.

    I will say, however, that except for sending email and doing things across multiple databases, it's usually a bad idea to have a stored procedure that handles only one row at a time.

    --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

  • Hi,

    I sometimes like to construct the code to execute the procedures with a script like the following, it has been very useful to me lot of times, but it has the disadvantage that you cannot do very much of error handling.

    -- Populate the parameters table. This step is not needed, since you alredy your temp table

    DECLARE @ParametersTable AS TABLE (Parameter1 VARCHAR(100),Parameter2 VARCHAR(100))

    INSERT INTO @ParametersTable

    SELECT 'Adrian','Facio' UNION ALL

    SELECT 'Karina','Barreras'

    -- Create the code to execute the procedure many times

    DECLARE @ExecuteProcedures AS VARCHAR(MAX)

    SET @ExecuteProcedures = CONVERT ( VARCHAR(MAX),

    (SELECT 'EXEC TheProducedureName '''+Parameter1+''','''+Parameter2+''' '

    AS 'text()'

    FROM @ParametersTable -- Your temp table

    FOR XML PATH ('')

    )

    )

    PRINT @ExecuteProcedures

    EXEC (@ExecuteProcedures)

  • Jeff Moden (1/16/2012)


    If the Temp Table has sequential numbering like that offered by an IDENTITY column and you've done nothing to disturb the sequence of numbers, then you could use a counter and WHILE loop to read each row into a variable and then exec the proc with the variable as a parameter to the proc.

    If your temp table does not yet have sequential numbering, you may want to add this to your definition:

    selectyour_col1, your_col2, ... , your_coln

    --here's the important part to be added

    , row_number() over(order by someColumn) as rowcnt

    into #TempTable

    from YourTable

    In the following while loop, you'd just update column rowcnt:

    while (

    select count(*)

    from #TempTable

    where rowcnt = 1) = 1

    begin

    --here's your original code

    --...

    --to step through the temp table, add the following

    update #TempTable

    set rowcnt = rowcnt - 1

    if (

    select count(*)

    from #TempTable

    where rowcnt = 1) = 0

    BREAK

    else

    CONTINUE

    end

    --you may want to add a message indicating you're done

    print 'Done running yourStoredProcedure.';

    Depending on how many rows your temp table has, check it out in a test environment to find out about the burden on your system.

    -Michael

    PS: Edited a typo.

  • adrian.facio (1/17/2012)


    Hi,

    I sometimes like to construct the code to execute the procedures with a script like the following, it has been very useful to me lot of times, but it has the disadvantage that you cannot do very much of error handling.

    -- Populate the parameters table. This step is not needed, since you alredy your temp table

    DECLARE @ParametersTable AS TABLE (Parameter1 VARCHAR(100),Parameter2 VARCHAR(100))

    INSERT INTO @ParametersTable

    SELECT 'Adrian','Facio' UNION ALL

    SELECT 'Karina','Barreras'

    -- Create the code to execute the procedure many times

    DECLARE @ExecuteProcedures AS VARCHAR(MAX)

    SET @ExecuteProcedures = CONVERT ( VARCHAR(MAX),

    (SELECT 'EXEC TheProducedureName '''+Parameter1+''','''+Parameter2+''' '

    AS 'text()'

    FROM @ParametersTable -- Your temp table

    FOR XML PATH ('')

    )

    )

    PRINT @ExecuteProcedures

    EXEC (@ExecuteProcedures)

    That won't work because you're missing a semi-colon to break up your batched statements. Try this: -

    -- Populate the parameters table. This step is not needed, since you alredy your temp table

    DECLARE @ParametersTable AS TABLE (Parameter1 VARCHAR(100),Parameter2 VARCHAR(100))

    INSERT INTO @ParametersTable

    SELECT 'Adrian','Facio' UNION ALL

    SELECT 'Karina','Barreras'

    -- Create the code to execute the procedure many times

    DECLARE @ExecuteProcedures AS VARCHAR(MAX)

    SELECT @ExecuteProcedures = tblsTxt.sqlCode

    FROM (SELECT '; EXEC TheProducedureName '''+Parameter1+''','''+Parameter2+''''

    FROM @ParametersTable -- Your temp table

    FOR XML PATH(''), TYPE) tbls(sqlCode)

    CROSS APPLY (SELECT STUFF(tbls.sqlCode.value('./text()[1]', 'VARCHAR(MAX)'),1,2,'')) tblsTxt(sqlCode)

    PRINT @ExecuteProcedures

    EXEC (@ExecuteProcedures)


    Forever trying to learn
    My blog - http://www.cadavre.co.uk/
    For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
    For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • Did you tried it before your change??

  • Another, usually better, option is break open the procedure and turn it into an operation that can take a whole table of "parameters" and work on them in a set-based method.

    Takes some work to set up, but is almost always worth it in the long run.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • adrian.facio (1/18/2012)


    Did you tried it before your change??

    Admittedly, the answer to that is "no".

    However IMO, to avoid ambiguity, whether or not your method works is irrelevant. It needs a semi colon appended to the end of each EXEC statement to show where each batch ends. This will also help to "future proof" the code.


    Forever trying to learn
    My blog - http://www.cadavre.co.uk/
    For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
    For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • Cadavre (1/18/2012)


    adrian.facio (1/18/2012)


    Did you tried it before your change??

    Admittedly, the answer to that is "no".

    However IMO, to avoid ambiguity, whether or not your method works is irrelevant. It needs a semi colon appended to the end of each EXEC statement to show where each batch ends. This will also help to "future proof" the code.

    I put semicolons at the end of my SQL statements for those exact reasons, but I disagree with your statement.

    They aren't needed, the dynamically generated statement isn't meant for human-eye consumption (no need to show where they end), and current versions of the SQL parser/compiler don't need them except in a few cases.

    Would I have included them in a dynamic script I wrote? Yes. But saying it's "wrong" to not have them and "whether or not your method works is irrelevant", is a bit strong in this case.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • well it works on my computer, but i agree that adding semicolons will make it better

  • GSquared (1/18/2012)


    Cadavre (1/18/2012)


    adrian.facio (1/18/2012)


    Did you tried it before your change??

    Admittedly, the answer to that is "no".

    However IMO, to avoid ambiguity, whether or not your method works is irrelevant. It needs a semi colon appended to the end of each EXEC statement to show where each batch ends. This will also help to "future proof" the code.

    I put semicolons at the end of my SQL statements for those exact reasons, but I disagree with your statement.

    They aren't needed, the dynamically generated statement isn't meant for human-eye consumption (no need to show where they end), and current versions of the SQL parser/compiler don't need them except in a few cases.

    Would I have included them in a dynamic script I wrote? Yes. But saying it's "wrong" to not have them and "whether or not your method works is irrelevant", is a bit strong in this case.

    I'll happily agree to disagree in this case I think.

    Although the dynamicSQL is not meant for human consumption, when digging into older code it's always helpful to add PRINT to see what has been generated. Without the semi-colons (and the CHAR(10) + CHAR(13) that I normally include, although that really is just preference) it can be extremely difficult to read.

    I will agree that saying it is "wrong" is a bit strong, but even so I'd insist on it being put "right" where I work πŸ˜€


    Forever trying to learn
    My blog - http://www.cadavre.co.uk/
    For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
    For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • Cadavre (1/18/2012)


    GSquared (1/18/2012)


    Cadavre (1/18/2012)


    adrian.facio (1/18/2012)


    Did you tried it before your change??

    Admittedly, the answer to that is "no".

    However IMO, to avoid ambiguity, whether or not your method works is irrelevant. It needs a semi colon appended to the end of each EXEC statement to show where each batch ends. This will also help to "future proof" the code.

    I put semicolons at the end of my SQL statements for those exact reasons, but I disagree with your statement.

    They aren't needed, the dynamically generated statement isn't meant for human-eye consumption (no need to show where they end), and current versions of the SQL parser/compiler don't need them except in a few cases.

    Would I have included them in a dynamic script I wrote? Yes. But saying it's "wrong" to not have them and "whether or not your method works is irrelevant", is a bit strong in this case.

    I'll happily agree to disagree in this case I think.

    Although the dynamicSQL is not meant for human consumption, when digging into older code it's always helpful to add PRINT to see what has been generated. Without the semi-colons (and the CHAR(10) + CHAR(13) that I normally include, although that really is just preference) it can be extremely difficult to read.

    I will agree that saying it is "wrong" is a bit strong, but even so I'd insist on it being put "right" where I work πŸ˜€

    Ok... now you're getting spooky. Are you sure you're not a long lost brother or something?

    --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

  • Cadavre (1/18/2012)


    Although the dynamicSQL is not meant for human consumption, when digging into older code it's always helpful to add PRINT to see what has been generated. Without the semi-colons (and the CHAR(10) + CHAR(13) that I normally include, although that really is just preference) it can be extremely difficult to read.

    I will agree that saying it is "wrong" is a bit strong, but even so I'd insist on it being put "right" where I work πŸ˜€

    For me, it's simple: after a fairly short acclimatisation period in a team I run you will be expected to insert char(13)+char(10) in strings of sql to make printed versions readable; if you don't, you will be reminded; if reminders don't work, bye-bye (that never happened, people that stupid don't get into the team in the first place). It's not just a preference any more than decent code layout is an just a preference in direct code (as opposed to executable strings) - that is, it's not an option at all, it's a mandatory requirement.

    Incidentally, using char(10)+char(13) (as you apparently do) instead is not acceptable - some people are still sometimes stuck with printing on old-fashioned typerwriter-style printers where that results in a horrible mess (I thank the powers that be that I haven't seen wide carriage printers where you might need char(13)+char(10)+char(0) to get it right since long before I got into SQL).

    Tom

  • L' Eomot InversΓ© (1/18/2012)


    Incidentally, using char(10)+char(13) (as you apparently do) instead is not acceptable

    I more meant that I include CR/LF, I didn't mean that I specifically wrote them as LF + CR but 10 comes before 13 so when writing in English I tend to write it down that way around (unless specifically asking one of the team to include them in some dynamic SQL).

    Jeff Moden (1/18/2012)


    Ok... now you're getting spooky. Are you sure you're not a long lost brother or something?

    Not sure if I could possibly have got a better compliment! I'm nowhere near up to your standards yet, but hopefully one day πŸ˜‰


    Forever trying to learn
    My blog - http://www.cadavre.co.uk/
    For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
    For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • Cadavre (1/18/2012)


    GSquared (1/18/2012)


    Cadavre (1/18/2012)


    adrian.facio (1/18/2012)


    Did you tried it before your change??

    Admittedly, the answer to that is "no".

    However IMO, to avoid ambiguity, whether or not your method works is irrelevant. It needs a semi colon appended to the end of each EXEC statement to show where each batch ends. This will also help to "future proof" the code.

    I put semicolons at the end of my SQL statements for those exact reasons, but I disagree with your statement.

    They aren't needed, the dynamically generated statement isn't meant for human-eye consumption (no need to show where they end), and current versions of the SQL parser/compiler don't need them except in a few cases.

    Would I have included them in a dynamic script I wrote? Yes. But saying it's "wrong" to not have them and "whether or not your method works is irrelevant", is a bit strong in this case.

    I'll happily agree to disagree in this case I think.

    Although the dynamicSQL is not meant for human consumption, when digging into older code it's always helpful to add PRINT to see what has been generated. Without the semi-colons (and the CHAR(10) + CHAR(13) that I normally include, although that really is just preference) it can be extremely difficult to read.

    I will agree that saying it is "wrong" is a bit strong, but even so I'd insist on it being put "right" where I work πŸ˜€

    I must not have written clearly.

    I'm not disagreeing with semicolons being an improvement. I'm disagreeing with the tennor of the statement you made. Or should I reword that to, "You're wrong, manners and respect are needed, and help to future-proof you against ... well ... something or other ... blah blah"? (Over the top for humor, not to offend, but to make a bit of a point.)

    All I'm saying is a bit more tact would have been good.

    Though I do also disagree somewhat with the use of "needed" in that statement. They aren't a necessity. A strong preference, and one that I share, but not a necessity. Syntactically correct T-SQL is a necessity. Formatting, readability, et al, are important, but not necessary. By definition of necessary/needed/necessity. But that's my editorial opinion on the use of the word, not a statement of incontrovertible fact.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Viewing 15 posts - 1 through 15 (of 20 total)

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