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

database-sequence, sql-server, t-sql

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

Problem

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 ```

Original source