一个存储过程的问题 求指点
描述： 假设有一张表，8个字段F1, F2, F3, F4, F5, F6, F7, F8。其中F1是表的自增长的关键字，F2是手工输入的多个编号【都是数字】（有两种格式，用"、“分隔或用”-“分隔，如果用”、“分隔，表示并列关系，比如1、3，表示1和3；用”-“分隔，表示从什么到什么，比如1-3，表示，1，2，和3。当然编号数目最多6个），F3到F8默认都是空的.
实现的功能：就是读取每一行，然后把该行的F2的多个编号拆分出来，然后按次序填入F3到F8字段中。
比如第一行是 F1 (1), F2 (5, 7)，则填入F3是5，F4是7，F6到F8继续留空
第二行是 F1 (2), F2 (5 - 7)，则填入F3是5, F4是6，F5是7， F6到F8继续留空
目前做的T-SQL为：
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:<Author,,Name>
-- Create date: <Create Date,,>
-- Description:<Description,,>
-- =============================================
ALTER PROCEDURE SplitData
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
declare @F1 INT
declare @F2 NVARCHAR(50)
declare @F3 TINYINT = NULL
declare @F4 TINYINT= NULL
declare @F5 TINYINT= NULL
declare @F6 TINYINT= NULL
declare @F7 TINYINT= NULL
declare @F8 TINYINT= NULL
declare @TAG NVARCHAR = NULL
declare @COUNT TINYINT = NULL
declare cursor1 cursor for
select F1,F2 from [DB0502].[dbo].[TB0502]
open cursor1
fetch next from cursor1 into @F1,@F2
while @@fetch_status=0
begin
set @F3=SUBSTRING(@F2,1,1);
set @TAG=SUBSTRING(@F2,2,1);
IF @TAG='、'
BEGIN
SET @F4 = SUBSTRING(@F2,3,1);
END
IF @TAG='-'
BEGIN
SET @COUNT = SUBSTRING(@F2,3,1) - @F3;
IF @COUNT = 5
BEGIN
SET @F4 = @F3+1;
SET @F5 = @F4+1;
SET @F6 = @F5+1;
SET @F7 = @F6+1;
SET @F8 = @F7+1;
END
IF @COUNT = 4
BEGIN
SET @F4 = @F3+1;
SET @F5 = @F4+1;
SET @F6 = @F5+1;
SET @F7 = @F6+1;
END
IF @COUNT = 3
BEGIN
SET @F4 = @F3+1;
SET @F5 = @F4+1;
SET @F6 = @F5+1;
END
IF @COUNT = 2
BEGIN
SET @F4 = @F3+1;
SET @F5 = @F4+1;
END
IF @COUNT = 1
BEGIN
SET @F4 = @F3+1;
END
END
UPDATE [DB0502].[dbo].[TB0502] SET F3=@F3,F4=@F4,F5=@F5,F6=@F6,F7=@F7,F8=@F8
where F1=@F1
fetch next from cursor1 into @F1,@F2
end
close cursor1
deallocate cursor1
END
GO
目前只5个字段，红色部分写了5个判断，如果有更多的话，就要再加判断，
有没有更好的处理方式，跪求大神指点疑惑！
[解决办法]
使用动态语句进行拼接，Exec sp_executesql
根据F2中的数量，动态的生成插入语句。最后执行语句就可以
[解决办法]
CREATE FUNCTION GetColValue(@F2 NVARCHAR(50) )
RETURNS NVARCHAR(100)
BEGIN
DECLARE @Lh NVARCHAR(50),@Lb NVARCHAR(50),@Ltmp NVARCHAR(50)
DECLARE @Pos INT ,@I INT
SELECT @Pos=CHARINDEX('-',@F2)
WHILE @Pos>0
BEGIN
SELECT @Lh=SUBSTRING(@F2,@Pos-1,1),@Lb=SUBSTRING(@F2,@Pos+1,1),@Ltmp=NULL
WHILE @Lh*1<=@Lb*1
SELECT @Ltmp=ISNULL(@Ltmp+'、','')+@Lh ,@Lh=ltrim(@Lh*1+1)
SELECT @F2=STUFF(@F2,@Pos-1,3,@Ltmp),@Pos=CHARINDEX('-',@F2)
END
SELECT @I=3,@F2=REPLACE(@F2,'、',',')+',',@Ltmp=''
WHILE @F2>''
BEGIN
SELECT @Ltmp=@Ltmp+ '[F'+Ltrim(@I)+']='+LEFT(@F2,CHARINDEX(',',@F2)-1)+',',@F2=RIGHT(@F2,LEN(@F2)-CHARINDEX(',',@F2))
SET @I=@I+1
END
RETURNSTUFF(@Ltmp,LEN(@Ltmp),1,'')
END
GO
exec MASTER.dbo.xp_execresultset 'SELECT ''UPDATE [TB0502] SET ''+dbo.GetColValue(F2)+'' WHERE [F1]=''+LTRIM(F1) FROM [TB0502]' ,'test'
GO
SELECT * FROM tb0502
--RESULT
/*F1 F2 F3 F4 F5 F6 F7 F8
----------- -------------------------------------------------- ---- ---- ---- ---- ---- ----
1 1、3 1 3 NULL NULL NULL NULL
2 2、4 2 4 NULL NULL NULL NULL
3 3、5 3 5 NULL NULL NULL NULL
4 1-3 1 2 3 NULL NULL NULL
5 2-4 2 3 4 NULL NULL NULL
6 3-5 3 4 5 NULL NULL NULL
7 3-8 3 4 5 6 7 8
（所影响的行数为 7 行）*/
[解决办法]
CREATE TABLE [dbo].[TB0502](
[F1] [int] IDENTITY(1,1) NOT NULL,
[F2] [nvarchar](50) NULL,
[F3] [tinyint] NULL,
[F4] [tinyint] NULL,
[F5] [tinyint] NULL,
[F6] [tinyint] NULL,
[F7] [tinyint] NULL,
[F8] [tinyint] NULL,
CONSTRAINT [PK_T0502] PRIMARY KEY CLUSTERED
([F1] ASC))
INSERT INTO [TB0502]([F2],[F3],[F4],[F5],[F6],[F7],[F8])
VALUES('1、3',null,null,null,null,null,null)
INSERT INTO [TB0502]([F2],[F3],[F4],[F5],[F6],[F7],[F8])
VALUES('2、4',null,null,null,null,null,null)
INSERT INTO [TB0502]([F2],[F3],[F4],[F5],[F6],[F7],[F8])
VALUES('3、5',null,null,null,null,null,null)
INSERT INTO [TB0502]([F2],[F3],[F4],[F5],[F6],[F7],[F8])
VALUES('1-3',null,null,null,null,null,null)
INSERT INTO [TB0502]([F2],[F3],[F4],[F5],[F6],[F7],[F8])
VALUES('2-4',null,null,null,null,null,null)
INSERT INTO [TB0502]([F2],[F3],[F4],[F5],[F6],[F7],[F8])
VALUES('3-5',null,null,null,null,null,null)
INSERT INTO [TB0502]([F2],[F3],[F4],[F5],[F6],[F7],[F8])
VALUES('3-8',null,null,null,null,null,null)
select F1,F2,F3,F4,F5,F6,F7,F8 from TB0502
/*
F1 F2 F3 F4 F5 F6 F7 F8
----------- -------------- ---- ---- ---- ---- ---- ----
1 1、3 NULL NULL NULL NULL NULL NULL
2 2、4 NULL NULL NULL NULL NULL NULL
3 3、5 NULL NULL NULL NULL NULL NULL
4 1-3 NULL NULL NULL NULL NULL NULL
5 2-4 NULL NULL NULL NULL NULL NULL
6 3-5 NULL NULL NULL NULL NULL NULL
7 3-8 NULL NULL NULL NULL NULL NULL
(7 row(s) affected)
*/
with t as
(select a.F1,
substring(a.F2,b.number,charindex('、',a.F2+'、',b.number)-b.number) 'Fv'
from TB0502 a, master.dbo.spt_values b
where charindex('、',a.F2,1)>0 and
b.type='P' and b.number between 1 and len(a.F2)
and substring('、'+a.F2,b.number,1)='、'
union all
select c.F1,d.number 'Fv'
from TB0502 c, master.dbo.spt_values d
where charindex('-',c.F2,1)>0 and d.type='P'
and d.number between substring(c.F2,1,charindex('-',c.F2)-1)
and substring(c.F2,charindex('-',c.F2)+1,50)
),
j as
(select g.F1,g.F2,g.number,h.Fv
from
(select e.F1,e.F2,f.number
from TB0502 e
cross join master.dbo.spt_values f
where f.type='P' and f.number between 3 and 8) g
left join
(select F1,Fv,
row_number() over(partition by F1 order by Fv)+2 'rn'
from t) h on g.F1=h.F1 and g.number=h.rn
)
update m
set m.F3=n.[3], m.F4=n.[4], m.F5=n.[5],
m.F6=n.[6], m.F7=n.[7], m.F8=n.[8]
from TB0502 m
inner join
(select F1,F2,[3],[4],[5],[6],[7],[8]
from j
pivot(max(Fv) for number in([3],[4],[5],[6],[7],[8])) k) n
on m.F1=n.F1 and m.F2=n.F2
select F1,F2,F3,F4,F5,F6,F7,F8 from TB0502
/*
F1 F2 F3 F4 F5 F6 F7 F8
----------- -------------- ---- ---- ---- ---- ---- ----
1 1、3 1 3 NULL NULL NULL NULL
2 2、4 2 4 NULL NULL NULL NULL
3 3、5 3 5 NULL NULL NULL NULL
4 1-3 1 2 3 NULL NULL NULL
5 2-4 2 3 4 NULL NULL NULL
6 3-5 3 4 5 NULL NULL NULL
7 3-8 3 4 5 6 7 8
(7 row(s) affected)
*/
