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