Oracle到SQL Server的转换

请问：
  朋友们，下面这段需要在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看生成的語句

  SQL code

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
  

SQL code

--大概这个样子

create procedure sync_dec_operator
(
@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)