>>See Record ID generator question Message #611104
>
>How to you call this procedure?
>
>Code in the above suggested message
>**********************
>*As long as you are going to retrieve all IDs from the same table, you no longer need to use Dynamic SQL.
>

>
>CREATE PROCEDURE gasp_generate
> @table_name	varchar(50),
> @newID int
>AS
>
>SET NOCOUNT ON
>
>UPDATE gasg_generator
>SET 
> @newID = id_value = id_value + 1
>WHERE
> table_name = @table_name
>
>IF @@ERROR <> 0
> RETURN 1
>ELSE
> RETURN 0
>

>
>Here I've changed the proc to return the next ID value using an OUTPUT parameter instead of return it as the return value. Within SQL Server, the convention is that the value returned from the proc represents the status.
>*****************************

You would call this procedure from the procedure that makes an insert into your table where you want to use this artificial counter.

E.g.

declare @ID int

declare @TableName varchar(100) = 'MyTableName'

declare @ParamDefinition nvarchar(1000)

set @ParamDefinition = N'@table_name varchar(100) ,@newID int OUTPUT'

execute sp_ExecuteSQL 'gasp_generate', @ParamDefinition, @table_Name = @TableName, @NewID = @ID OUTPUT

insert into myTableName (ID, ...) values (@ID, ...)