SQL Server sequence set current value

sequence, sql-server

Solution

As has been mentioned, this would require dynamic SQL since alter sequence requires a constant for the `restart` argument.

You might do something like this, then:

DECLARE @sth bigint;
SET @sth = 1000;
DECLARE @sql nvarchar(max);
SET @sql = N'ALTER SEQUENCE StreamEntrySequence RESTART WITH ' + cast(@sth as nvarchar(20)) + ';';
EXEC SP_EXECUTESQL @sql;

Problem

I am using SQL Server 2012 (v11.0.2100) and I want to create a sequence that starts with a random (dynamic) number but I wasn't able to do this, also I put my effort to find a good solution for this but I haven't found something that will satisfy me. The case that I tried and failed: ``` DECLARE @sth bigint SET @sth = 1000 ALTER SEQUENCE StreamEntrySequence RESTART WITH @sth; ``` Error : Incorrect syntax near '@sth' An ugly solution ``` declare @sth bigint; declare @i bigint; SET @sth = 100000 ; while @i<@sth; BEGIN SET @i= next value for StreamEntrySequence; END ``` Is there other way to set the current value or the start value to a random value? Maybe using server procedures?

Original source