sql server: Procedure to create sequence

Question

I wanna create a stored procedure to check if a sequence is with a given name is exist or no to create it or alter its value to max(Id) selected from given table name, So I create the below procedure but it throws a syntax error.

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

Please add the details of the syntax error and samples about the input and output. — Marco Aurelio Fernandez Reyes
– Marco Aurelio Fernandez Reyes, Commented Feb 25, 2020 at 14:12
You will have to use dynamic sql for this. But I have to ask, why do you need a procedure to create a sequence in the first place? This sounds like something in the design has gone a bit left of center. And why do you need to restart a sequence with the current value? Also your syntax looks mostly like sql server but you have some datatypes that are NOT sql server (varchar2, number). And no idea what the body of your if statement is doing, it is only a partial statement. — Sean Lange
– Sean Lange, Commented Feb 25, 2020 at 14:15
The variables referenced inside the single quotes will be literals. One way would be to use Dynamic SQL and create the string correctly to execute it. — Dave Brown
– Dave Brown, Commented Feb 25, 2020 at 14:18

David Browne - Microsoft · Accepted Answer · 2020-02-25 14:29:40Z

You have to use dynamic SQL to both find the current max ID and to create or alter the SEQUENCE objects. Something like this:

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

Collectives™ on Stack Overflow

sql server: Procedure to create sequence

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related