Post reply

don't start one statement until the previous is finished

  • March 3, 2015 at 6:47 pm

    #298245

    I am working with a stored procedure that needs to roll up a week number column once a week - columns are numbered 1-10, 1 being this week, 2 being last week and so forth

    once a week the 10th column is deleted, the 9th becomes 10, the 8th becomes the 9th and so forth

    and the 1st is calculated

    the week numbers are getting all screwed up - and we think it's because one statement starts before the one before it completes

    the statements go like this:

    delete theTable where week_num=10;

    update theTable set weeknum=10 where weeknum=9;

    update theTable set weeknum=9 where weeknum=8;

    and so forth

    is that the reason? is there any way not to start one statement until the one before it finishes?

    thanks

  • March 3, 2015 at 10:15 pm

    #1781529

    Do you have the code of the proc? It could be many causes like simultaneous execs of the proc, programming logic or even a variant of the Halloween problem. Is there an index on this weeknumber column?

    I don't think that statements are started before the previous is finished.

    Ddl of related tables would also help us help you.

  • March 4, 2015 at 4:00 am

    #1781569

    SQL is linear, so it wont start a statement until the previous has completed, there could be an issue with transactions, overlapping, or not completing in time for the next statement starts, especially if you are using NOLOCK hints on your queries, but would need to see the code in full.

    I know this is going to sound daft but why not just do

    DELETE FROM TheTable where week_num=10

    UPDATE theTable

    SET week_num=Week_num+1

    Insert new week Into TheTable

    _________________________________________________________________________
    SSC Guide to Posting and Best Practices

Viewing 3 posts - 1 through 2 (of 2 total)

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