数据库lib7第4题创建存储过程

 1 create proc insert_data
 2     @str1 varchar(MAX),
 3     @str2 varchar(MAX)
 4 as
 5 begin
 6     declare @len1 int,
 7             @len2 int,
 8             @pos1 int,
 9             @pos2 int,
10             @start_point1 int,
11             @start_point2 int,
12             @subString1 varchar(MAX),
13             @subString2 varchar(MAX)
14     select @len1 = len(@str1);
15     select @len2 = len(@str2);
16     select @start_point1 = 1;
17     select @start_point2 = 1;
18     while (1 > 0) 
19     begin 
20         select @pos1 = charindex(‘;‘, @str1, @start_point1); 
21         select @pos2 = charindex(‘;‘, @str2, @start_point2);
22         if (@pos1 = 0 and @pos2 = 0)
23         begin
24             select @subString1 = substring(@str1, @start_point1, @len1-@start_point1+1);
25             select @subString2 = substring(@str2, @start_point2, @len2-@start_point2+1);
26             insert into Kc values(@subString1, @subString2);
27             break;
28         end
29         else if (@pos1 = 0)
30         begin 
31             select @subString1 = NULL;
32             select @subString2 = substring(@str2, @start_point2, @pos2-@start_point2);
33         end 
34         else if (@pos2 = 0)
35         begin
36             select @subString2 = NULL;
37             select @subString1 = substring(@str1, @start_point1, @pos1-@start_point1);
38         end
39         else 
40         begin
41             select @subString1 = substring(@str1, @start_point1, @pos1-@start_point1);
42             select @subString2 = substring(@str2, @start_point2, @pos2-@start_point2);
43         end
44         insert into Kc values(@subString1, @subString2);
45         select @start_point1 = @pos1 + 1;
46         select @start_point2 = @pos2 + 1;
47         if (@start_point1 > @len1 and @start_point2 > @len2)
48             break
49     end
50 end