大家来看看这个存储过程该怎样写？－－难难难．
字符串 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
--結果
/*
IDbidsid
13633
23634
335-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
[解决办法]
declare @s varchar(8000)
create table #table(id int identity(1,1),qs int,jz int)
set @s = '36,33 ¦36,34 ¦35,-1 ¦ '
set @s = left(@s,len(@s)-len( '¦ '))
set @s= 'insert into #table(qs,jz) select '+ replace(@s, '¦ ', ' union all select ')
exec (@s)
select * from #table
drop table #table
（所影响的行数为 4 行）
id qs jz
----------- ----------- -----------
1 36 33
2 36 34
3 35 -1
4 -1 -1
[解决办法]
-----拆字符函数
CREATE FUNCTION dbo.f_splitSTR(
@s varchar(8000), --要分拆的字符串
@split varchar(10), --数据分隔符
@pos int -- 取第几个
)RETURNS varchar(100)
AS
BEGIN
DECLARE @splitlen int, @re varchar(100)
SET @splitlen=LEN(@split+ 'a ') - 2
WHILE CHARINDEX(@split,@s) > 0 AND @pos > 0
SELECT
@re = LEFT(@s,CHARINDEX(@split,@s)-1),
@s=STUFF(@s,1,CHARINDEX(@split, @s)+@splitlen, ' '),
@pos = @pos - 1
RETURN(CASE
WHEN @pos = 0 THEN @re
WHEN @pos = 1 THEN @s
ELSE NULL END)
END
----统计符号函数
CREATE FUNCTION dbo.f_count_sub_string
(@var varchar(500) ,
@split char(2) )
RETURNS int AS
BEGIN
declare @cur char(1)
declare @i int
select @i = 0
declare @total int
select @total = len(@var)
declare @num int
select @num = 0
while @i <=@total
begin
select @i = @i + 1
select @cur = substring(@var,@i,1)
if @cur = @split
select @num = @num+1
end
return @num
END
---存储过程
CREATE PROCEDURE [dbo].[zp_cool](@str as varchar(100))
AS
declare @sign_count as int
declare @i as int ----计数标志
declare @tmp as varchar(100)
declare @bid as varchar(20)
declare @sid as varchar(20)
set @i = 1
delete from kind
select @sign_count=dbo.f_count_sub_string(@str, '& ') --判断含有&符号的个数
while(@i <=@sign_count)
begin
select @tmp=dbo.f_splitSTR(@str, '¦ ',@i)
select @bid=dbo.f_splitSTR(@tmp, ', ',1)
set @sid = right(@tmp,(len(@tmp)-len(@bid)+1))
insert into kind( id,bid,sid)
values(@i,@bid,@sid)
set @i= @i+1
end
GO
---执行
exec zp_cool '36,33 ¦36,34 ¦35,-1 ¦ '
[解决办法]
---参照chuifengde(树上的鸟儿)的方法，加多一个参数
---创建表
Create Table Kind(id int identity(1,1),
username Varchar(10),bid int,sid int)
Go
----创建存储过程
Create Proc Pro_Test
@Name Varchar(10),
@Str Varchar(1000)
As
Declare @y Varchar(8000)
Select @y=Replace(@Str, '¦ ', ' union all select ' ' '+@Name+ ' ' ', ')
Set @y= 'Insert Into Kind(username,bid,sid)
Select '+Left( ' ' ' '+@Name+ ' ' ', '+@y,Len(@y)-Len( ' union all select '))
Exec(@y)
Go
---调用存储过程
Exec Pro_Test 'aa ', '36,33 ¦36,34 ¦35,-1 ¦ '
---查询结果
Select * From Kind
---结果
/*
id username bid sid
----------- ---------- ----------- -----------
1 aa 36 33
2 aa 36 34
3 aa 35 -1
（所影响的行数为 3 行）
*/
