sql-server - 为什么整数变量不能按顺序用作START WITH的值

Question

我正在尝试为序列创建动态起始号，但它不接受变量viz。 @START_SEQ代表START WITH。请考虑以下代码:-

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

Answer 1

您可以对动态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

如下面的 ta.speot.is所指出的(谢谢!)， CREATE SEQUENCE的语法采用常量(请参见 MSDN)。