DECLARE @array VARCHAR(max) SET @array = '1,2,3' 这里用个什么函数实现下边的查询 select * from tb where ID=1; select * from tb where ID=2; select * from tb where ID=3;
create function f_split(@SourceSql varchar(8000),@StrSeprate varchar(10)) returns @temp table(a varchar(100)) --实现split功能 的函数 --date :2003-10-14 as begin declare @i int set @SourceSql=rtrim(ltrim(@SourceSql)) set @i=charindex(@StrSeprate,@SourceSql) while @i>=1 begin insert @temp values(left(@SourceSql,@i-1)) set @SourceSql=substring(@SourceSql,@i+1,len(@SourceSql)-@i) set @i=charindex(@StrSeprate,@SourceSql) end if @SourceSql<>'' insert @temp values(@SourceSql) return end select * from dbo.f_split('1,2,3,4',',')a -------------------- 1 2 3
DECLARE @array VARCHAR(max) SET @array = '1,2,3' select @array=' select * from tb where id='+replace(@array,',',' union all select * from tb where id=') exec(@array)
returns @temp table(a varchar(100))
--实现split功能 的函数
--date :2003-10-14
as
begin
declare @i int
set @SourceSql=rtrim(ltrim(@SourceSql))
set @i=charindex(@StrSeprate,@SourceSql)
while @i>=1
begin
insert @temp values(left(@SourceSql,@i-1))
set @SourceSql=substring(@SourceSql,@i+1,len(@SourceSql)-@i)
set @i=charindex(@StrSeprate,@SourceSql)
end
if @SourceSql<>''
insert @temp values(@SourceSql)
return
end
select * from dbo.f_split('1,2,3,4',',')a
--------------------
1
2
3
DECLARE @array VARCHAR(max)
SET @array = '1,2,3'
select @array=' select * from tb where id='+replace(@array,',',' union all select * from tb where id=')
exec(@array)