sqlserver 存储过程传入参数已逗号区分，执行结果生成程序集循环插入临时表

一 首先创建存储过程

 --传入参数是类似（‘one,two,three,’）
 create procedure pro_contract(@sum nvarchar(1000))  --pro_contract 为过程名 ，由于要传入的参数大，定义范围为1000

  as

       declare @num int --定义数组初始值

       declare @next int  --剩余字符串

       declare @now nvarchar(30)  --当前截取字符， nvarchar(30) 类型可视情况变

      set @num=1
        while(@num<len(@sum))
   begin

      set @next=charIndex(',',@sum,@num)

if(@next>0)

set @now=cast(substring(@sum,@num,@[email protected]) as nvarchar(30))

--下面开始查询语句，已我应用为例如下

SELECT 

a.fld_ContractNo as 合同编号,
e.fld_name as 学生库姓名,
a.fld_QYDate as 签约日期,
d.BAS_A1_40 as 留学国家,
c.BAS_A1_40 as 合同类型,
a.fld_JinE as 合同金额,
n.SYS_A2_20 as 分公司区域,
b.SYS_A1_40 as 咨询顾问,
e.fld_insertDate as 学生库录入时间,
f.SYS_A1_40 as 学生库录入人,
g.BAS_A1_40 as 学生库资源类型,
j.BAS_A1_40 as 途径,
h.BAS_A1_40 as 学生状态,
i.fld_name as 咨询人,
k.SYS_A1_40 as 录入人,
l.BAS_A1_40 as 资源类型,
i.fld_rtm as 录入时间
  FROM [WisewayCPCS].[dbo].[tbl_contract] a
  left join [WisewayCPCS].[dbo].[SYS_A1] b on a.fld_ConsultantID=b.SYS_A1_10
  left join [WisewayCPCS].[dbo].[BAS_A1] c on a.fld_ContractType=c.BAS_A1_30
  left join [WisewayCPCS].[dbo].[BAS_A1] d on a.fld_CountryId=d.BAS_A1_30
  left join [WisewayCPCS].[dbo].[tbl_student] e on a.fld_StudentId=e.fld_studentId
  left join [WisewayCPCS].[dbo].[SYS_A1] f on e.fld_LuRuRenId=f.SYS_A1_10
  left join [WisewayCPCS].[dbo].[BAS_A1] g on e.fld_type=g.BAS_A1_30
  left join [WisewayCPCS].[dbo].[BAS_A1] h on e.fld_status=h.BAS_A1_30
  left join [WisewayCPCS].[dbo].[tbl_DianHuaJiLu] i on cast(e.fld_studentId as nvarchar)=i.fld_studentIds
  left join [WisewayCPCS].[dbo].[BAS_A1] j on i.fld_tujing=j.BAS_A1_30
  left join [WisewayCPCS].[dbo].[SYS_A1] k on i.fld_rby=k.SYS_A1_10
  left join [WisewayCPCS].[dbo].[BAS_A1] l on i.fld_type=l.BAS_A1_30
  left join [WisewayCPCS].[dbo].[SYS_R1] m on a.fld_ConsultantID=m.SYS_R1_10

  left join [WisewayCPCS].[dbo].[SYS_A2] n on m.SYS_R1_20=n.SYS_A2_10

  where fld_ContractNo like '%'[email protected]+'%'   [email protected]就是符合查询条件的字符

set @[email protected]+1  --循环查询下一个字符

    end
else

      break

end

二 创建临时表

create table #tmp(
列名1 nvarchar(30)，
列名2 nvarchar(30),
......

)

三 插入临时表

--在插入之前要先清空临时表数据

delete from #tmp

--然后执行存储过程插入临时表

insert into #tmp exec pro_contract (WH1UK28024,CD1AU28035,GM3UK28092,) --()内为自己要传入的参数值

--查询结果

select * from #tmp