Michael,

Here's a snippet from a SQL stored procedure that we use to do just that.

UPDATE <table_name>
   SET <field_name> = <field_name> + 1
       @return_value = <field_name> + 1
   WHERE ...

By incrementing the SQL column and the @return_value within the same UPDATE statement, you avoid possible conflicts in a multi-user environment since the UPDATE statement will lock the underlying table momentarily.

Hope this helps,
Kurt
>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.