将字符串变量转换成临时表

1.由于数据库中不存在数组，所以我们在需要使用数组时往往采用字符串加分割符的方式来间接表达数组，常用的形式如下：‘1001,1002,1003,100004,106,‘   注意最后的逗号不能          省略，将用于判断字符串结束标志。

具体例子如下：

declare 
  @vc_op_code     varchar(8000),
  @vc_project_id  varchar(8000),
  @l_left_index           int,   
  @l_right_index          int  

select
  @vc_op_code    = '1001,1002,1003,1004,1005,1006,',--加逗号保护
  @vc_project_id = '2001,2002,2003,2004,',          --加逗号保护
  @l_left_index  = 1,   
  @l_right_index = 1

  
--存放柜员编号临时表
create table #op_codes
(
   l_op_code int default 0  
)

--存放项目编号临时表
create table #project_ids
(
    l_project_id  int  default 0
)

--生成柜员号表
 while  charindex(',',@vc_op_code,@l_left_index)<>0
 begin
    select  @l_right_index=charindex(',',@vc_op_code,@l_left_index)
    insert into #op_codes (l_op_code) values(SUBSTRING(@vc_op_code,@l_left_index,@l_right_index-@l_left_index))
    set @l_left_index = @l_right_index+1
 end
 
 set  @l_left_index  = 1  
 set  @l_right_index = 1
 
--生成项目编号表
while  charindex(',',@vc_project_id,@l_left_index)<>0
 begin
    select  @l_right_index=charindex(',',@vc_project_id,@l_left_index)
    insert into #project_ids (l_project_id) values(SUBSTRING(@vc_project_id,@l_left_index,@l_right_index-@l_left_index))
    set @l_left_index = @l_right_index+1
 end
 
select *  from #op_codes
select *  from #project_ids
 
drop table #op_codes
drop table #project_ids