求助:oracle函数转换为sqlserver函数问题

本人正在进行一个oracle迁移到sqlserverr的项目,其中涉及一个函数,修改后无法创建

oracle代码:

CREATE FUNCTION [test].[GETWORKORDERID] (numberPre varchar2)
  return varchar2 is
  PRAGMA AUTONOMOUS_TRANSACTION;
  findId     number(8); --maxid
  nowNumber  varchar2(50);
  n_count    number(8);
  n_count2   number(8);
  nowNumber2 varchar2(50);
begin

  nowNumber := to_char(sysdate, 'yyyymmdd');

  nowNumber2 := to_char(sysdate - 1, 'yyyymmdd');

  select count(1)
    into n_count2
    from user_sequences t
   where t.sequence_name = 'SEQ_' || numberPre || '_' || nowNumber2;

  if n_count2 > 0 then
    execute immediate 'drop sequence SEQ_' || numberPre || '_' ||
                      nowNumber2;
  end if;

  select count(1)
    into n_count
    from user_sequences t
   where t.sequence_name = 'SEQ_' || numberPre || '_' || nowNumber;

  if n_count = 0 then
    execute immediate 'create sequence SEQ_' || numberPre || '_' ||
                      nowNumber ||
                      ' minvalue 10000 maxvalue 99999999 start with 10000 increment by 1 NOCYCLE NOCACHE';
  end if;

  execute immediate 'select SEQ_' || numberPre || '_' || nowNumber ||
                    '.nextval from dual'
    into findId;

  commit;
  return numberPre || nowNumber || findId;
end;

我转换为sqlserver代码:

CREATE FUNCTION [ZZC-CSR].[GETWORKORDERID] (@numberPre nvarchar(max))
returns table 
as 
begin
  DECLARE @findId    bigint;
  DECLARE @nowNumber bigint;
  DECLARE @n_count  bigint;
  DECLARE @n_count2  bigint;
  DECLARE @nowNumber2 bigint;
  DECLARE @sql1 nvarchar(max);
  DECLARE @sql2 nvarchar(max);
  DECLARE @sql3 nvarchar(max);
  DECLARE @sql4 nvarchar(max);
  DECLARE @result nvarchar(max);
  set @nowNumber=convert(varchar(30),getdate() ,20)
  set @nowNumber2=convert(varchar(30),getdate() ,20)
  select count(1)
    into n_count2
    from user_sequences t
   where t.sequence_name = 'SEQ_' + numberPre + '_' + nowNumber2;

  if n_count2 > 0 begin
    set @sql1= 'drop sequence SEQ_' + numberPre + '_' + nowNumber2;
    execute (@sql1) 
  end ;

  select count(1)
    into n_count
    from user_sequences t
   where t.sequence_name = 'SEQ_' + @numberPre + '_' + @nowNumber;

  if n_count = 0 begin
    set @sql2='create sequence SEQ_ ' + @numberPre + '_' + @nowNumber +
                      ' minvalue 10000 maxvalue 99999999 start with 10000 increment by 1 NOCYCLE NOCACHE';
    execute (@sql2)
  end ;

    set @sql3='select SEQ_ ' + @numberPre + '_' + @nowNumber + '.NEXT VALUE FOR user_sequences';
    set @findId=@sql3;
    set @sql4=@numberPre + @nowNumber + @findId;
    insert into @result exec (@sql4)
end;

报错信息:
消息 102,级别 15,状态 31,过程 GETWORKORDERID,行 43 [批起始行 0]
“BEGIN”附近有语法错误。

求助哪里有问题。

1个回答

Csdn user default icon
上传中...
上传图片
插入图片
抄袭、复制答案,以达到刷声望分或其他目的的行为,在CSDN问答是严格禁止的,一经发现立刻封号。是时候展现真正的技术了!
立即提问
相关内容推荐