Dmitry,

If several users are doing this activity I see lots of deadlocks in your future. You need to rethink this design because it's fairly unfriendly to the way SQL backends work.

>Let me explain more of what I am doing and why scope_identity() may not be applicable in my case.
>
>I have a generic routine where an array of one or more (easily could be 100) rows is created. Each row of the array has an expression, either UPDATE or INSERT. The method that receives this array scan through the rows and executes each command (being UPDATE or INSERT). (And of course I use TRANSACTION so that if any one command failes it will be ROLLed back). Some of the UPDATE commands need to have a value of IDENTITY (PK) from some other INSERT command. So for these cases the UPDATE expression has a "place holder" string in a form of a call to a method that gets the IDENTIFY from a different table and then the program replaces/inserts this value into the UPDATE expression. Since there is difficult to predict in which order the rows of the array will be processed (that is, there could be an INSERT between INSERT that I need to get the IDENTITY value and the UPDATE where this value is necessary, I am choosing to use INDENT_CURRENT which gives me the value. I have not tested the entire routine yet; will be doing it this week.