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

来源：互联网发布：icloud照片怎么导入mac 编辑：程序博客网时间：2024/06/14 11:54

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_codesselect *  from #project_ids drop table #op_codesdrop table #project_ids

0 0