> Begin Tran
> Update counter set my_id = my_id + 1
> select @my_id = my_id from counter
> Commit Tran
> return (@my_id)

Usually, this can be converted in

UPDATE counter
SET my_id = my_id + 1, @my_id = my_id + 1

RETURN (@my_id)