大家来看看这个存储过程该怎样写？－－难难难．
字符串     s     =     "36,33     ¦36,34,     ¦35,-1     ¦ ";    
怎样在表kind中形成:    
id                 bid                 sid    
1                     36                     33    
2                     36                     34    
3                     35                     -1    
 
请问用存储过程该怎样写.多谢了.    


------解决方案--------------------
create table kind(id int identity(1,1),bid int,sid int)
go
create proc proc_test
@s varchar(1000)
as

declare @y varchar(8000)
select @y=replace(@s, '¦ ', ' union all select ')
set @y= 'insert kind select '+left(@y,len(@y)-len( ' union all select '))
exec(@y)
go

exec proc_test '36,33 ¦36,34 ¦35,-1 ¦ '
select * from kind
------解决方案--------------------
Declare @S Varchar(2000)
Select @S = '36,33 ¦36,34 ¦35,-1 ¦ '

Select Top 1000 ID = Identity(Int, 1, 1) Into #T From Syscolumns A, Syscolumns B

Select
ID = Identity(Int, 1, 1),
Left(Col, CharIndex( ', ', Col) - 1) As bid,
Stuff(Col, 1, CharIndex( ', ', Col), ' ') As sid
Into kind
From
(
Select
Col = Substring(@S, A.ID, CharIndex( '¦ ', @S + '¦ ', A.ID) - A.ID)
From #T A
Where Substring( '¦ ' + @S, A.ID, 8) = '¦ '
) A
Where RTrim(Col) != ' '

Select * From kind

Drop Table #T
Drop Table kind
GO
--結果
/*
ID bid sid
1 36 33
2 36 34
3 35 -1
*/
------解决方案--------------------
declare @result table (id int identity(1,1), bid int, sid int)
declare @s varchar(1000), @bid int, @sid int
set @s = '36,33 ¦36,34 ¦35,-1 ¦ '
while charindex( '¦ ', @s) > 0
begin
set @bid = left(@s, charindex( ', ', @s) - 1)
set @sid = substring(@s, charindex( ', ', @s) + 1, charindex( '¦ ', @s) - charindex( ', ', @s) - 1)
set @s = right(@s, len(@s) - charindex( '¦ ', @s) - len( '¦ ') + 1)
insert @result select @bid, @sid
end
select * from @result

------解决方案--------------------
create table kind(id int identity(1,1),bid int,sid int)
go
create proc proc_test
@s varchar(1000)
as

declare @y varchar(8000)
select @y=replace(@s, '¦ ', ' union all select ')
set @y= 'insert kind select '+left(@y,len(@y)-len( ' union all select '))
exec(@y)


楼上的代码很好了
go

exec proc_test '36,33 ¦36,34 ¦35,-1 ¦ '
select * from kind