Execute a procedure with parameter from table, one by one

  • My goal was to run a procedures feeding it a parameter from an input table, executing it once for each record. Can you give me advice on how to improve it? I came up with this:

    begin
    declare @Vertrag as int = 1
    set @Vertrag = (select min(ref_nr_vertrag)
    from tbl_111_aed2_umhaengen_Verträge)
    while @Vertrag <= -- do while contract number is lower than the highest
    (select max(ref_nr_vertrag) from tbl_111_aed2_umhaengen_Verträge)
    begin -- While
    exec usp_PLP_und_PRP_Aktualisierung_ASAP 0, 2020, '%', @vertrag
    set @Vertrag = -- set to next highest number
    (select coalesce(min(ref_nr_vertrag), 9999999) -- exit clause, no contract numbers higher than 2020999 by definition
    from tbl_111_aed2_umhaengen_Verträge
    where ref_nr_vertrag > @Vertrag)
    print 'Update contract Nr:' + cast(@vertrag as nvarchar(7))
    end --While
    end -- procedure

    thank you for  any suggestions.

    Christoph Kauflin
    working in Controlling, but trying to program the tools we need to do that

  • Why does it need to be improved - does it not work?

    I would start by making sure you have the same number of ENDs as BEGINs.  Then I would consider rewriting usp_PLP_und_PRP_Aktualisierung_ASAP so that it processes all the rows in one go, not one at a time.  Without being able to see that stored procedure, though, it's difficult to advise.

    John

  • Thanks for your answer. I just thought, maybe there is more elegant way to do something like this, I started writing T-SQL only a year ago and am eager to learn. The stored procedure can be be run in one go (I omitted the last parameter for simplicity, it is a range). But we (would) have to rewrite it in the future with table locks and/or transctions ... but that's another story.

    Christoph Kauflin
    working in Controlling, but trying to program the tools we need to do that

  • As a general rule of thumb, you want to avoid looping.

    So, I would be looking to rewrite the proc that is being called from the loop to process the data in batches

  • thanks for your answer. I googled batch mode and I guess I need some more experience and knowledge before I'd be able to transform my code into batch mode.

    Christoph Kauflin
    working in Controlling, but trying to program the tools we need to do that

  • christoph.kauflin wrote:

    thanks for your answer. I googled batch mode and I guess I need some more experience and knowledge before I'd be able to transform my code into batch mode.

    Probably not what was meant.  Before MS bastardized the meaning of what a lot of people meant as "batch mode", it meant the opposite of doing things row by row in a loop.

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

  • p.s.  You might want to post the stored procedure and the contents (or a least a sample of) the control table.  See the first link in my signature line below for how to do the last part of that in the best way to help us help you.

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

  • Thanks for your suggestions. I think the procedure is not my main problem, because it allows to update records , for the whole company for one year, for a cost unit, for a range of contracts, for budgets, actual cost or both and any combination. But because it is the main table also used for reporting we run into nasty problems where the delete part would be executed, but not the insert part ... which meant the budget for 2020 was empty until we could run the procedure again. We scared quite some people this way. I must that we are amateurs (read your quote), learning by doing, and have to (re-)program and update several programs from database design up to user interface ... magically it works and I even got a promotion 😉 The procedure calls 4 (sub)procedures which delete and append to different tables. I guess it would be too much to post all 5 procedures and extracts from the 4 tables they get data from or delete and insert into.

     

     

    Christoph Kauflin
    working in Controlling, but trying to program the tools we need to do that

  • Your query would be more efficient if you used a cursor to select distinct ref_nr_vertrag from table tbl_111_aed2_umhaengen_Verträge. Like this:

    DECLARE @Vertrag AS int= 1
    DECLARE @myCursor cursor
    SET @myCursor = cursor FOR SELECT DISTINCT ref_nr_vertrag FROM tbl_111_aed2_umhaengen_Verträge
    OPEN @myCursor
    FETCH NEXT FROM @myCursor INTO @Vertrag
    WHILE @@FETCH_STATUS = 0 BEGIN
    exec usp_PLP_und_PRP_Aktualisierung_ASAP 0, 2020, '%', @vertrag
    FETCH NEXT FROM @myCursor INTO @Vertrag
    print 'Update contract Nr:' + cast(@vertrag as nvarchar(7))
    END
    CLOSE @myCursor
    DEALLOCATE @myCursor

    Without seeing the code in the stored procedure, or what it does, it's not possible to say, but you might be able to rewrite the stored procedure as a function. In which case you could call it with one SQL statement using CROSS APPLY.

    ;WITH CTE AS 
    (
    SELECT DISTINCT ref_nr_vertrag
    FROM tbl_111_aed2_umhaengen_Verträge
    )
    SELECT *
    FROM CTE
    CROSS APPLY func_PLP_und_PRP_Aktualisierung_ASAP (0, 2020, '%', CTE.ref_nr_vertrag)
  • thanks. looks good. I will look into this soon.

    Christoph Kauflin
    working in Controlling, but trying to program the tools we need to do that

  • A function cannot do an UPDATE.    But for just retrieving data, I agree with the cross apply approach.

    What Jeff and others are trying to explain is that if your procedure just updates one row at a time, it can be improved.    A source table can be joined to a target table to update many rows all at once.    This is called "set based processing" and is generally faster than looping to call a procedure multiple times,  updating each row one by one.    It is also easier and involves less code once you get the knack of it.

     

     

     

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

  • Jeff Moden wrote:

    christoph.kauflin wrote:

    thanks for your answer. I googled batch mode and I guess I need some more experience and knowledge before I'd be able to transform my code into batch mode.

    Probably not what was meant.  Before MS bastardized the meaning of what a lot of people meant as "batch mode", it meant the opposite of doing things row by row in a loop.

    In my experience its always been sort of the opposite of "interactive", and CERTAINLY batch jobs did not prevent loops.

    https://en.wikipedia.org/wiki/Batch_processing

     

Viewing 12 posts - 1 through 11 (of 11 total)

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