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

Question

本人正在进行一个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”附近有语法错误。

求助哪里有问题。

Answer 1

http://www.myexception.cn/sql-server/1284307.html