Ahhh... Much cleaner than mine!
I think I had spent so much time learning how to make the stored procedure work with the Dynamic SQL that I was bound and determined to use it regardless of whether or not I actually needed it!
All kidding aside, your suggestion is exactly what I was looking for. Thanks so much for your help!
- Kile
>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