I must have mis-understood how SP's worked! I thought the transaction would be kept track of while other transactions are allowed to proceed, and only applied after you 'commit'.

If this is the case, it is possible to be in the midst of your transaction wile other folks are doing the same transaction - and your returned value would be duplicated by someone elses returned value - no?

Of course, you're doing the select immediately after the update so the odds are in your favor...

Please let me know if I'm off base with my understanding!

>My Michael
>
>I do this with a sp. You will have to translate this into tsql. It has been too long since I have used it.
>
>

>begin
>declare @counter integer ;
>Begin transaction ;
>update table set counter = counter+1 ;
>-- this locks the row so another user can't get the value
>select counter into @counter from table ;
>commit ;
>end
>

>
>>>>Howdy, y'all
>>>>
>>>>I'd like to T-SQL to MS-SQL to have it increment a field value and return that new value in one swipe - rather than hitting it twice (trying to eliminate the possibility of duplication).
>>>>
>>>>I suspect I can do a transaction - but I'm afraid that won't really work if someone else is incrementing the same counter at the same time.
>>>>
>>>>How can I do that?
>>>
>>>text to m.lcCommand noshow
>>>insert into ...
>>>endtext
>>>
>>>SQLExec(m.lnHandle, m.lcCommand)
>>>SQLExec(m.lnHandle, "select @@identity",'lastIdent')
>>>
>>>Cetin
>>
>>I don't think that will do it if the SQL field is not the primary key... it's just a regular old integer value with no index, trigger or anything. The table is just a single record with a field for each counter.