时间戳

首页 > 解决方案 > sql server：创建序列的过程

sql-server - sql server：创建序列的过程

问题描述

我想创建一个存储过程来检查是否存在具有给定名称的序列以创建它或将其值更改为从给定表名称中选择的 max(Id)，因此我创建了以下过程，但它引发了语法错误.

CREATE PROCEDURE EBPP_CORE.CREATE_OR_ALTER_SEQUENCE
    @sequence_name VARCHAR2,
    @table_name VARCHAR2
AS
  BEGIN
   DECLARE @MAX_ID_VAL NUMBER;
   DECLARE @sequence_exist NUMBER;
   SELECT @MAX_ID_VAL = CAST(ISNULL(MAX(id) + 1, 1) as nvarchar(10)) FROM @table_name ;
   SELECT @sequence_exist = COUNT(*) FROM sys.sequences  WHERE name = @sequence_name;
  IF @sequence_exist>0
   ' ALTER SEQUENCE  @sequence_name  RESTART WITH  @MAX_ID_VAL';
  ELSE
    'CREATE SEQUENCE @sequence_name  START WITH @MAX_ID_VAL  INCREMENT BY 1' ;
  END

标签： sql-serverstored-proceduressequence

解决方案

您必须使用动态 SQL 来查找当前最大 ID 并创建或更改 SEQUENCE 对象。像这样的东西：

CREATE OR ALTER PROCEDURE EBPP_CORE.CREATE_OR_ALTER_SEQUENCE
    @sequence_name nvarchar(128),
    @table_name nvarchar(128)
AS
/*

drop table if exists foo
go
create table foo(id int)
exec EBPP_CORE.CREATE_OR_ALTER_SEQUENCE 'foo_seq','foo'

insert into foo(id) values (3)
exec EBPP_CORE.CREATE_OR_ALTER_SEQUENCE 'foo_seq','foo'

*/
BEGIN
   DECLARE @MAX_ID_VAL bigint;
   DECLARE @sequence_exist bigint;

   declare @sql nvarchar(max) = concat('select @MAX_ID_VAL = MAX(id) + 1 FROM ', quotename(@table_name));

   print @sql;
   exec sp_executesql @sql, N'@MAX_ID_VAL bigint output', @MAX_ID_VAL=@MAX_ID_VAL output;

   set @MAX_ID_VAL = coalesce(@MAX_ID_VAL,1)

   SELECT @sequence_exist = COUNT(*) FROM sys.sequences  WHERE name = @sequence_name;
    IF @sequence_exist>0
    BEGIN
     set @sql =  concat('ALTER SEQUENCE  ', quotename(@sequence_name), ' RESTART WITH  ', @MAX_ID_VAL) ;
     print @sql;
     exec (@sql);
    END
    ELSE
    BEGIN
     set @sql =  concat('CREATE SEQUENCE  ', quotename(@sequence_name), ' START WITH  ', @MAX_ID_VAL,' INCREMENT BY 1') ;
     print @sql;
     exec (@sql);
    END
END

推荐阅读