写过程时碰到这么一个问题,需要在库里处理N条数据,不想在程序里循环造成多次访问数据库
而SQL Sever又不支持数组参数,尝试了两种方法,在此做笔记如下(省略了单条数据的处理,用#temp是为了显示结果)
1.利用replace
而SQL Sever又不支持数组参数,尝试了两种方法,在此做笔记如下(省略了单条数据的处理,用#temp是为了显示结果)
1.利用replace
create table #temp
(
ss varchar( 200 ) not null
)
declare @str varchar( 200 )
declare @result varchar( 1000 )
set @str = ' aaa,bb,c,d,e,ffffff '
set @result = ' insert into #temp(ss) select ''' + replace(@str, ' , ' , ''' union select ''' ) + ''''
exec(@result)
select * from #temp
(
ss varchar( 200 ) not null
)
declare @str varchar( 200 )
declare @result varchar( 1000 )
set @str = ' aaa,bb,c,d,e,ffffff '
set @result = ' insert into #temp(ss) select ''' + replace(@str, ' , ' , ''' union select ''' ) + ''''
exec(@result)
select * from #temp
2.利用charindex和substring
create table #temp
(
ss varchar( 200 ) not null
)
declare @str varchar( 200 )
declare @curr int
declare @prev int
set @str = ' aaa,bb,c,d,e,ffffff '
set @curr = 1
set @prev = 1
while @prev < len(@str)
begin
set @curr = charindex( ' , ' ,@str,@prev)
if @curr > @prev
insert #temp select substring(@str,@prev,@curr - @prev)
else
begin
insert #temp select substring(@str,@prev,len(@str) - @prev + 1 )
break
end
set @prev = @curr + 1
end
select * from #temp
(
ss varchar( 200 ) not null
)
declare @str varchar( 200 )
declare @curr int
declare @prev int
set @str = ' aaa,bb,c,d,e,ffffff '
set @curr = 1
set @prev = 1
while @prev < len(@str)
begin
set @curr = charindex( ' , ' ,@str,@prev)
if @curr > @prev
insert #temp select substring(@str,@prev,@curr - @prev)
else
begin
insert #temp select substring(@str,@prev,len(@str) - @prev + 1 )
break
end
set @prev = @curr + 1
end
select * from #temp