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.