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

Question

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

Looks like the `FROM` keyword is missing in your inner query — Raj, Nov 18 '13 at 07:05
What happens if a new user is added to `MASTER_USER` between reading the `MAX(USER_ID)` value and creating the sequence? — ta.speot.is, Nov 18 '13 at 07:33
Side note: you should **not** use the `sp_` prefix for your stored procedures. Microsoft has [reserved that prefix for its own use (see *Naming Stored Procedures*)](http://msdn.microsoft.com/en-us/library/ms190669%28v=sql.105%29.aspx), and you do run the risk of a name clash sometime in the future. [It's also bad for your stored procedure performance](http://www.sqlperformance.com/2012/10/t-sql-queries/sp_prefix). It's best to just simply avoid `sp_` and use something else as a prefix - or no prefix at all! — marc_s, Oct 25 '17 at 12:05

score 13 · Accepted Answer · edited May 23 '17 at 12:02

13

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

edited May 23 '17 at 12:02

Community

1
1

answered Nov 18 '13 at 07:31

Szymon

42,577
16
96
114

4

I believe this is the only way to do it: [the syntax for `START WITH` takes a ``](http://technet.microsoft.com/en-us/library/ff878091.aspx). – ta.speot.is Nov 18 '13 at 07:34
3

Works well except variable @sql. I had to convert @START_SQL into nvarchar `SET @sql = 'CREATE SEQUENCE [dbo].[SEQ_USER_ID] AS [bigint] START WITH ' + CONVERT(NVARCHAR(10), @START_SEQ) + 'INCREMENT BY 1 MINVALUE 1 MAXVALUE 99999999 CACHE` – Camille Oct 30 '18 at 12:09

score 4 · Answer 2 · answered Jan 02 '19 at 10:22

4

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

answered Jan 02 '19 at 10:22

Ahmed Salem

1,687
22
26

`@maxBookingId + 1` ? – LukStorms Jan 02 '19 at 10:52
No sequence itself make the incremental operation :) – Ahmed Salem Jan 02 '19 at 11:21
Oh right, normally the code that uses the sequence would use a NEXT VALUE. So it's fine if that sequence starts with the current maximum id. – LukStorms Jan 02 '19 at 11:29

Leonardo Martínez Medici · Answer 3 · 2022-10-19T14:56:09.897

0

AS INTEGER in create sequence is not necessary

declare @maxBookingId as int 
select @maxBookingId = max(bookingid) from booking
declare @s nvarchar(4000);
set @s = N'
CREATE SEQUENCE Invoice_Seq 
START WITH ' + cast(@maxBookingId as nvarchar) + '
INCREMENT BY 1  
NO CYCLE;'
EXEC (@s);

This script is perfect to integrate in incremental distribution

edited Oct 19 '22 at 14:56

answered Sep 15 '22 at 14:31

Leonardo Martínez Medici

11
1

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

3 Answers3