请问:
朋友们,下面这段需要在SQL Server中运行该如何转换。我的难点是在 变量和赋值的处理上。
先在此,谢谢大家! create procedure sync_dec_operator(m_ems_no varchar2, m_i_e_flag varchar2) is
m_sql varchar2(2000);
begin
m_sql := 'insert into T_ems1(seq_no,i_e_flag,trade_code,ems_no)
select t.seq_no,t.i_e_flag,t.trade_co,t.manual_no from T_ems' ||
m_i_e_flag ||
'_head t where not exists (select 0 from T_ems1 t1 where t1.seq_no = t.seq_no)
and (t.manual_no = :ems_no or
exists (select 0 from T_ems t1 where t1.ems_no = :ems_no))';
execute immediate m_sql
using m_ems_no, m_ems_no; execute immediate m_sql
using m_ems_no, m_i_e_flag;
朋友们,下面这段需要在SQL Server中运行该如何转换。我的难点是在 变量和赋值的处理上。
先在此,谢谢大家! create procedure sync_dec_operator(m_ems_no varchar2, m_i_e_flag varchar2) is
m_sql varchar2(2000);
begin
m_sql := 'insert into T_ems1(seq_no,i_e_flag,trade_code,ems_no)
select t.seq_no,t.i_e_flag,t.trade_co,t.manual_no from T_ems' ||
m_i_e_flag ||
'_head t where not exists (select 0 from T_ems1 t1 where t1.seq_no = t.seq_no)
and (t.manual_no = :ems_no or
exists (select 0 from T_ems t1 where t1.ems_no = :ems_no))';
execute immediate m_sql
using m_ems_no, m_ems_no; execute immediate m_sql
using m_ems_no, m_i_e_flag;
在Print看生成的語句 CREATE PROCEDURE sync_dec_operator(
@m_ems_no NVARCHAR(200),
@m_i_e_flag NVARCHAR(200)
)
AS
DECLARE @m_sql nvarchar(2000)
BEGIN
SET @m_sql='insert into T_ems1(seq_no,i_e_flag,trade_code,ems_no)
select t.seq_no,t.i_e_flag,t.trade_co,t.manual_no from T_ems'
+@m_i_e_flag+
'_head t where not exists (select 0 from T_ems1 t1 where t1.seq_no = t.seq_no)
and (t.manual_no = @m_ems_no or
exists (select 0 from T_ems t1 where t1.ems_no = @m_ems_no))'
PRINT @m_sql--顯示語句
EXECUTE sp_executesql @m_sql,'@m_ems_no NVARCHAR(200)',@m_ems_no
END
(
@m_ems_no varchar(max),
@m_i_e_flag int
)
as
begin
declare @m_sql varchar(max)
set @m_sql='
insert into
T_ems1(seq_no,i_e_flag,trade_code,ems_no)
select
t.seq_no,t.i_e_flag,t.trade_co,t.manual_no
from
T_ems'+ltrim(@m_i_e_flag)+'+head t
where
not exists (select 0 from T_ems1 t1 where t1.seq_no = t.seq_no)
and
t.manual_no='+@m_ems_no+'
or
exists (select 0 from T_ems t1 where t1.ems_no ='+@m_ems_no+')'exec (@m_sql)