哎,算了我自问自答好了。USE tempdb; GO IF OBJECT_ID('spInsc') IS NOT NULL DROP PROCEDURE spInsc; GO CREATE PROCEDURE spInsc AS BEGIN DECLARE @m CHAR(5),--b2中提取的字符串 @b1 INT , @b2 VARCHAR(50),--产品合同月份 @n INT,--b2中字符串间隔 @nc INT --b2中字符串的数量 DECLARE cur CURSOR FOR SELECT DISTINCT b1,b2 FROM b; OPEN cur; FETCH NEXT FROM cur INTO @b1,@b2; WHILE @@FETCH_STATUS=0 BEGIN /*查找b2中字符串的数量*/ SELECT @nc=LEN(b2) FROM b WHERE b1=@b1; SET @n=1; WHILE @n<=@nc BEGIN SET @m=SUBSTRING(@b2,@n,1); INSERT INTO c VALUES (@b1, CASE @m WHEN 'A' THEN '10' WHEN 'B' THEN '11' WHEN 'C' THEN '12' ELSE @m END); SET @n=@n+2; END; FETCH NEXT FROM cur INTO @b1,@b2; END; CLOSE cur; DEALLOCATE cur; END;还可以这样写:USE tempdb; GO /****** Object: StoredProcedure [dbo].[InsertToTablec] Script Date: 04/13/2012 11:19:34 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ------------------------------------ --用途: ------------------------------------ Create PROCEDURE [dbo].[InsertToTablec] AS begin tran declare @error int set @error=0 declare T1 cursor for select b1,b2 from dbo.b declare @b1 int,@b2 varchar(50) open T1 fetch next from T1 into @b1,@b2 while @@FETCH_STATUS=0 begin declare @id varchar(300),@m int,@n int ,@count int set @m=CHARINDEX(',',@b2) set @n=1 set @count=0 WHILE @m>0 BEGIN set @id=substring(@b2,@n,@m-@n)
insert into c (c1,c2) values ( @b1,case @id when 'A' then '10' when 'B' then '11' when 'C' then '12' else @id end)
set @error=@error+@@ERROR set @n=@m+1 set @m=CHARINDEX(',',@b2,@n) END
if(@n<LEN(@b2)+1) begin set @id=SUBSTRING(@b2,@n,LEN(@b2)-@n+1)
insert into c (c1,c2) values ( @b1,case @id when 'A' then '10' when 'B' then '11' when 'C' then '12' else @id end)
end fetch next from T1 into @b1,@b2 end close T1 deallocate T1--提交 if @error=0 begin commit tran end else begin rollback tran end
GO
IF OBJECT_ID('spInsc') IS NOT NULL
DROP PROCEDURE spInsc;
GO
CREATE PROCEDURE spInsc
AS
BEGIN
DECLARE @m CHAR(5),--b2中提取的字符串
@b1 INT ,
@b2 VARCHAR(50),--产品合同月份
@n INT,--b2中字符串间隔
@nc INT --b2中字符串的数量
DECLARE cur CURSOR FOR SELECT DISTINCT b1,b2 FROM b;
OPEN cur;
FETCH NEXT FROM cur INTO @b1,@b2;
WHILE @@FETCH_STATUS=0
BEGIN
/*查找b2中字符串的数量*/
SELECT @nc=LEN(b2) FROM b WHERE b1=@b1;
SET @n=1;
WHILE @n<=@nc
BEGIN
SET @m=SUBSTRING(@b2,@n,1);
INSERT INTO c
VALUES (@b1,
CASE @m
WHEN 'A' THEN '10'
WHEN 'B' THEN '11'
WHEN 'C' THEN '12'
ELSE @m END);
SET @n=@n+2;
END;
FETCH NEXT FROM cur INTO @b1,@b2;
END;
CLOSE cur;
DEALLOCATE cur;
END;还可以这样写:USE tempdb;
GO
/****** Object: StoredProcedure [dbo].[InsertToTablec] Script Date: 04/13/2012 11:19:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
------------------------------------
--用途:
------------------------------------
Create PROCEDURE [dbo].[InsertToTablec] AS
begin tran
declare @error int
set @error=0
declare T1 cursor for select b1,b2 from dbo.b
declare @b1 int,@b2 varchar(50)
open T1
fetch next from T1 into @b1,@b2
while @@FETCH_STATUS=0
begin
declare @id varchar(300),@m int,@n int ,@count int
set @m=CHARINDEX(',',@b2)
set @n=1
set @count=0
WHILE @m>0
BEGIN
set @id=substring(@b2,@n,@m-@n)
insert into c (c1,c2) values ( @b1,case @id when 'A' then '10' when 'B' then '11' when 'C' then '12' else @id end)
set @error=@error+@@ERROR
set @n=@m+1
set @m=CHARINDEX(',',@b2,@n)
END
if(@n<LEN(@b2)+1)
begin
set @id=SUBSTRING(@b2,@n,LEN(@b2)-@n+1)
insert into c (c1,c2) values ( @b1,case @id when 'A' then '10' when 'B' then '11' when 'C' then '12' else @id end)
end
fetch next from T1 into @b1,@b2
end
close T1
deallocate T1--提交
if @error=0
begin
commit tran
end
else
begin
rollback tran
end