Post reply

Need suggestions on multiple insertions SP

  • April 27, 2005 at 2:12 am

    #66053

    Hello,

    I have an SP where there are a series of insertions in different tables. The first table has an autonumber field. I use this to insert in other tables. These insertions are all done inside a transaction. Now there is a possibiliy that multiple clients call this SP at the same time.

    Will there be any inconsistency?

    I mean, say the first client calls first and while the SP is being executed and before the transaction commits, another request comes in, what will the value of the autonumber for the second execution?

    What will happen if the first transaction fails?

    I am currently using @@IDENTITY to pick up the autonumber. I found that it is suggested that SCOPE_IDENTITY or IDENT_CURRENT should be used. Which of the two will be the better option?

    Thanks and Regards,

    Dinuj Nath

  • April 27, 2005 at 2:32 am

    #555126

    I would go with SCOPE_IDENTITY(). There is a great example on the differences between these three when you look at BOL for IDENT_CURRENT.

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • April 27, 2005 at 2:36 am

    #555127

    From how you have described your process, you should use SCOPE_IDENTITY to get the number.

    A number assigned by an identity property is always used regardless of whether or not the transaction succeeds. So when your first transaction is given the next number (lets say it is 100) that number will be used, and the next transaction will be given the next number (101). If the first transaction is rolled back then the number that was given to it (100) will be 'thrown away' and there will not be any row with that number for that column.

    --
    Chris Hedgate http://www.hedgate.net/
    Contributor to the Best of SQL Server Central volumes
    Articles: http://www.sqlservercentral.com/columnists/chedgate/

  • April 27, 2005 at 2:57 am

    #555131

    Thanks to all,

    I am also thinking of doing away with the autonumber (Due to problems with replication)and doing the it manually in the SP. Will it be safe in the scenario I have described with 'N' clients?

    Thanks and Regards,

    Dinuj Nath

  • April 27, 2005 at 5:39 am

    #555161

    If you do your own 'number-generator' the right way, then yes, it will be safe. Here's a thread where you can find a method how to increment and assign a new number in a single update statement.

    http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=174305#bm174355

    Just don't forget to thoroughly test your code as well.

    /Kenneth

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

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