This seems like a perfectly reasonable attempt to implement a Sequence Table to me.

The code can be simplified:

-- Sequence table, holds next PK per table

CREATE TABLE dbo.GPK

(

table_name SYSNAME NOT NULL PRIMARY KEY,

next_value INTEGER NOT NULL

);

GO

-- Example row

INSERT dbo.GPK (table_name, next_value) VALUES (N'dbo.MyTable', 0);

GO

-- Procedure to allocate a new PK

CREATE PROCEDURE dbo.usp_GenPK(@table_name SYSNAME, @NewID INT OUTPUT)

AS

BEGIN

UPDATE dbo.GPK

SET @NewID = next_value = next_value + 1

WHERE table_name = @table_name;

END;

GO

-- Test

DECLARE @NewID INTEGER;

EXEC dbo.usp_GenPK N'dbo.MyTable', @NewID OUTPUT;

SELECT @NewID;

edit: forgot the schema prefix