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