数据转换

Question

create procedure pr_InsPurview
@CaseID int,
@casemember varchar(50)
as
declare @i int
while @casemember<>''
begin
set @i=charindex(',',@casemember+',')
insert Purview(caseID,Purview)
values(@CaseID,left(@casemember,@i-1))
set @casemember=substring(@casemember,@i+1,50)
end

Answer 1

--测试--测试表
create table Purview(caseID int,Purview int)
go--存储过程
create procedure pr_InsPurview
@CaseID int,
@casemember varchar(50)
as
declare @i int
while @casemember<>''
begin
set @i=charindex(',',@casemember+',')
insert Purview(caseID,Purview)
values(@CaseID,left(@casemember,@i-1))
set @casemember=substring(@casemember,@i+1,50)
end
go--调用
exec pr_InsPurview @CaseID=2,@casemember="3,5,6,11"
go--显示结果
select * from Purview
go--删除测试
drop table Purview
drop proc pr_InsPurview/*--测试结果
caseID      Purview     
----------- ----------- 
2           3
2           5
2           6
2           11（所影响的行数为 4 行）
--*/