See Re: Record ID generator question Thread #605839

>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