Mike,

Segey Berezniker just gave me a reference to your answer. The code is pleasingly compact.

How should I modify your code so that record addition is automatic? In other words, so that a new record is created in the keys table if a call is made with the name of a table that is not yet in the table.

Also, what precaution needs to be taken to make sure the search is independent of the case (upper/lower) of the tablename?

TIA,

Alex

>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.
>
>-Mike