[SOLVED] Why is an integer variable not accepted as a value for START WITH in sequence

Why is an integer variable not accepted as a value for START WITH in sequence

I am trying to create dynamic start number for sequence but it is not accepting variable viz. @START_SEQ for START WITH. Please consider following code : -

CREATE PROCEDURE  [dbo].[SP_RESET_SEQ]
AS
DECLARE @START_SEQ INT =0;

BEGIN
SET @START_SEQ = (SELECT MAX(USER_ID)+1 FROM MASTER_USER);
IF OBJECT_ID('SEQ_USER_ID') IS NOT NULL
DROP SEQUENCE [dbo].[SEQ_USER_ID]

CREATE SEQUENCE [dbo].[SEQ_USER_ID] 
 AS [bigint]
 START WITH @START_SEQ
 INCREMENT BY 1
 MINVALUE 1
 MAXVALUE 99999999
 CACHE 
END

Solution

You can do the same with dynamic SQL:

CREATE PROCEDURE  [dbo].[SP_RESET_SEQ]
AS
DECLARE @START_SEQ INT =0;

BEGIN
SET @START_SEQ = (SELECT MAX(USER_ID)+1 FROM MASTER_USER);
IF OBJECT_ID('SEQ_USER_ID') IS NOT NULL
DROP SEQUENCE [dbo].[SEQ_USER_ID]

DECLARE @sql NVARCHAR(MAX)

SET @sql = 'CREATE SEQUENCE [dbo].[SEQ_USER_ID] 
 AS [bigint]
 START WITH ' + @START_SEQ
 + 'INCREMENT BY 1
 MINVALUE 1
 MAXVALUE 99999999
 CACHE'

 EXEC(@sql) 
END

As noted by ta.speot.is below (thanks!), the syntax for CREATE SEQUENCE takes a constant (see MSDN).