SQL Server 序列设置当前值 [英] SQL Server sequence set current value
问题描述
我正在使用 SQL Server 2012 (v11.0.2100),我想创建一个以随机(动态)数字开头的序列,但我无法做到这一点,我也努力寻找一个好的解决方案为此,但我还没有找到能让我满意的东西.
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;
错误:
'@sth' 附近的语法不正确
Incorrect syntax near '@sth'
一个丑陋的解决方案
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?
推荐答案
如前所述,这将需要动态 SQL,因为 alter sequence 要求 restart
参数的常量.
As has been mentioned, this would require dynamic SQL since alter sequence requires a constant for the restart
argument.
你可以这样做,然后:
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;
这篇关于SQL Server 序列设置当前值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!