Why is an integer variable not accepted as a value for START WITH in sequence
Another example as the above one not working with me
declare @maxBookingId as int
select @maxBookingId = max(bookingid) from booking
declare @s nvarchar(4000);
set @s = N'
CREATE SEQUENCE Invoice_Seq AS INTEGER
START WITH ' + cast(@maxBookingId as nvarchar) + '
INCREMENT BY 1
NO CYCLE;'
EXEC (@s);
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).