>I have developed this SP based in suggestions seen at this forum.
>This code runs with any table and column of D.B.
>I have done it to avoid to use the IDENTITY
>In some conditions, I can not use IDENTITY.
>I have tried it, and it works fine, but anyway, I would like to have your option and know that I can improve it.
>

>SET ANSI_NULLS ON
>SET QUOTED_IDENTIFIER ON
>go
>
>ALTER PROCEDURE [dbo].[Nextcode]
>        @myTable varchar(50),
>        @myColumn varchar(50)
>AS
>
>SET NOCOUNT ON
>
>DECLARE
>    @return_val int,
>    @sqlcmd nvarchar(100)
>
>BEGIN TRAN t1
>SET @sqlcmd = 'update ' + @myTable + ' set ' + @myColumn + ' = ' + @myColumn + ' + 1'
>EXEC sp_executesql @sqlcmd
>SET @sqlcmd = 'select ' + @myColumn + ' as myID from ' + @myTable
>EXEC sp_executesql @sqlcmd, N'@return_val int output', @return_val output
>if @@ERROR > 0
>	BEGIN
>		ROLLBACK TRAN t1
>		SET @return_val = -1
>		RETURN @return_val
>	END
>ELSE
>	BEGIN
>		COMMIT TRAN t1
>		RETURN @return_val
>	END
>

>
>Thanks in advance

Ricardo,

There is no where clause in the UPDATE command. You will be bumping the value in every row in the table. The error trapping structure in the code will miss all errors that occur before

EXEC sp_executesql @sqlcmd, N'@return_val int output', @return_val output

.

In general this technique breaks down in a high concurrency system where you may have hundreds of simultaneous users with thousands of locks. You cannot set a unique constraint on the column because it will contain a NULL before the new value is set. You will also be performing two extra queries for each update (a 66% performance reduction).