create table test(code varchar(20),desc varchar(20)) gocreate procedure sp_test(@str varchar(8000)) as begin declare @str1 varchar(40)
if right(@str,1)!=';' set @str=@str+';'
while charindex(';',@str)>0 begin set @str1=left(@str,charindex(';',@str)-1) set @str =stuff(@str,1,charindex(';',@str),'')
insert into test(code,desc) values(left(@str1,charindex(',',@str1)-1),stuff(@str1,1,charindex(',',@str1),'')) end end goexec sp_test 'aaaa,bbbb;cccc,dddd;eeee,ffff;gggg,hhhh' goselect * from test godrop procedure sp_test drop table test go
同上 declare @s1 varchar(8000),@s2 varchar(8000) set @s1='1;2;3;4;5;6'create table t(id int) set @s2=' insert t select '+replace(@s1,';',' union all select ') exec (@s2) select * from t /* id ----------- 1 2 3 4 5 6 */ drop table t
能说详细一点吗?或者给点提示让我去找资料.
gocreate procedure sp_test(@str varchar(8000))
as
begin
declare @str1 varchar(40)
if right(@str,1)!=';'
set @str=@str+';'
while charindex(';',@str)>0
begin
set @str1=left(@str,charindex(';',@str)-1)
set @str =stuff(@str,1,charindex(';',@str),'')
insert into test(code,desc)
values(left(@str1,charindex(',',@str1)-1),stuff(@str1,1,charindex(',',@str1),''))
end
end
goexec sp_test 'aaaa,bbbb;cccc,dddd;eeee,ffff;gggg,hhhh'
goselect * from test
godrop procedure sp_test
drop table test
go
declare @s1 varchar(8000),@s2 varchar(8000)
set @s1='1;2;3;4;5;6'create table t(id int)
set @s2=' insert t select '+replace(@s1,';',' union all select ')
exec (@s2)
select * from t
/*
id
-----------
1
2
3
4
5
6
*/
drop table t
--------------------------------------------------------------
把存储过程内部的";"或","替换成"<br>"即可。