Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Join Date
    Mar 2007
    Posts
    77

    Question Unanswered: stored procedure inquiry...

    Hi to all,

    I have asked this kind of question from another forum, which is the xtremevbtalk, but with little luck, only one has replied to my inquiry. I hope any of you can give me ideas. My question is this:


    Do stored procedures execute exclusively on the calling program/thread (whatever you call that to the process that called the stored procedure)?

    I mean if I have a stored procedure that adds a record in a certain table, and then it retrieves the autonumber of that newly inserted record (like the @@IDENTITY in SQL SERVER), and then use that retrieved ID for use in another table, will it work correctly? (Assuming that the stored proc is bug free.)

    Is that good to implement in a multi-user environment? What happens if another user called that stored procedure too? User A called the stored procedure, and in a split second, User B called it too? Is it possible that User B will abe able to retrieve the ID of the newly inserted record that was made by User A?

    Is stored procedures a good way to resolve concurrency issues? If not, maybe anyone could give me some ideas or clues how to approach solving concurrency and multi-user issues in database programming.

    Thanks and god bless!

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    You can have many copies of a stored procedure running at one time. The only increase in overhead (and it is small) is a plan may need to be generated for the "extra" users.
    In SQL Server, @@identity, and scope_identity() return only the identity values generated in the current SPID (User process). In Oracle, users queue up on the Sequence, but that queue runs pretty quickly.

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by MCrowley
    You can have many copies of a stored procedure running at one time. The only increase in overhead (and it is small) is a plan may need to be generated for the "extra" users.
    In SQL Server, @@identity, and scope_identity() return only the identity values generated in the current SPID (User process). In Oracle, users queue up on the Sequence, but that queue runs pretty quickly.


    @@IDENTITY...be bad, be berry berry bad
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Mar 2007
    Posts
    77
    @@IDENTITY...be bad, be berry berry bad
    what was that supposed to mean?

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Moved to SQL Server forum
    Quote Originally Posted by Panoy
    what was that supposed to mean?
    Look up @@identity and scope_identity() in BoL. They are subtly different and @@identity is rarely the correct choice.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by pootle flump
    Moved to SQL Server forum
    Look up @@identity and scope_identity() in BoL. They are subtly different and @@identity is rarely the correct choice.

    Subtle differences?

    Using @@IDENTITY, you could grab up the wrong value
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by Brett Kaiser
    Using @@IDENTITY, you could grab up the wrong value
    The same is true of scope_identity() - it all depends on exactly what you want to grab
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    So far as I have seen, @@identity really only gets tripped up, if you have triggers or sub-procedures that insert into tables with identity columns.

  9. #9
    Join Date
    May 2007
    Posts
    49
    As per my knowledge, out three -
    SCOPE_IDENTITY, IDENT_CURRENT and @@IDENTITY
    using SCOPE_IDENTITY is the best way to avoid concurrency issues but after all what pootle flump said is true -
    "it all depends on exactly what you want to grab."
    Mihir Mehendale
    DB Team @ Clarion
    Clarion Technologies
    (SEI CMMI Level 3)
    Pune, India
    www.clariontechnologies.co.in
    www.vEmployee.com

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by mihirclarion
    after all what pootle flump said is true -
    "it all depends on exactly what you want to grab."
    A pedant after my own heart
    Testimonial:
    pootle flump
    ur codings are working excelent.

  11. #11
    Join Date
    Mar 2007
    Posts
    77
    how could the @@Identity grap the wrong value? An example may be appropriate for my peace of mind.

    Thanks and god bless all!

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Have a look at scope_identity and @@identity in BoL. It makes the very point about the distinction.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  13. #13
    Join Date
    Nov 2005
    Posts
    122
    Quote Originally Posted by Panoy
    how could the @@Identity grap the wrong value? An example may be appropriate for my peace of mind.

    Thanks and god bless all!
    @@IDENTITY will also be affected by identity inserts done by triggers.
    If you for example enable merge replication for a database, three new triggers will be added for each replicated table. These triggers will do inserts into replication system tables with identity values. If you use @@IDENTITY to get the identity value from your own tables, you will now end up getting the identity value from the system table insert.

    SCOPE_IDENTITY() is not affected by inserts performed by underlying triggers.

    Therefore you should NEVER use @@IDENTITY. It is leftover garbage from SQL Server 7.

  14. #14
    Join Date
    Mar 2007
    Posts
    77
    sorry, but what is BoL?

  15. #15
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by Panoy
    sorry, but what is BoL?
    Good grief - most imporant thing you have learned here so far. BoL = Books Online = SQL Server documentation.

    BoL is extremely thorough and actually a very useful help file (believe it or not). It should be your first port of call any time you come across something you don't know. Once you are a grizzled dba you can come on these boards and bore everyone with how many times you have read it
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •