legacy_sql_sequence_gen.sql

CREATE SCHEMA [common] AUTHORIZATION [dbo]


CREATE TABLE [common].[sequence_test_id]
(
	 sequence_id bigint identity(1,1)
	,CONSTRAINT [pk_sequence_test_id] PRIMARY KEY (sequence_id)
)
GO

IF OBJECT_ID('') IS NULL
	EXEC ('CREATE PROCEDURE [common].[usp_generate_test_id] AS SELECT 1')
GO

CREATE PROCEDURE [common].[usp_generate_test_id]
AS
BEGIN
	
	SET NOCOUNT ON
	
	DECLARE @sequence_value bigint
    
	INSERT INTO [common].[sequence_test_id] DEFAULT VALUES
     
    SET @sequence_value = SCOPE_IDENTITY()
     
    DELETE FROM [common].[sequence_test_id] WITH (READPAST)

	RETURN @sequence_value


END;