I have a table called "Nums", this has the last values for all ID in others database tables. For example, Nums.invoice has the last ID invoice number inserted in table called "Invoices".
I need to simulate the IDENTITY behavior, but I don't (and I can't) use this feature.
I need create a store procedure that, passing it a parameter with the name of the column that I want to increment.

I made this code but, it doesn't work.
--------------------------

declare @myCol varchar(100)
declare @qry varchar(256)
declare @next numeric(10)

set @next = -9
set @myCol = 'invoice'

begin transaction T1
set @qry = 'select @next = ' + @myCol + ' + 1 from Nums'
exec(@qry)
set @qry = 'update Nums set ' + @myCol+ ' = @next'
if @@ERROR = 0
begin
commit transaction T1
print 'Ok. ID ' + convert(varchar,@next)
end
else
begin
rollback transaction T1
print 'error ' + convert(varchar,@@ERROR)
end

--------------------------
I haven't very clear the error handling and how to return the ID incremented.

Everything this within a transaction (It's a multiple user app) to avoid lack of normalization with the new ID.

Thanks in advance