--函数 create function fn_Ids( @dqfzlid int ) returns varchar(200) as begin declare @Ids varchar(200) set @Ids='' select @Ids=@Ids+','+rtrim(id) from tab where dqfzlid =@dqfzlid if @Ids<>'' set @Ids=stuff(@Ids,1,1,'') return @Ids end go--存储过程 create proc pr_Query @Ids varchar(200) as exec('select dqfzlid,dbo.fn_Ids(dqfzlid) as Id from tab where id in ('+@Ids+') group by dqfzlid') go--调用 exec pr_Query '68,80,81'
--环境 create table test ( id int, did int ) insert into test select 68, 70 insert into test select 69, 70 insert into test select 70, 70 insert into test select 80, 69 insert into test select 81, 69 --建立辅助函数 create function f_connstr ( @col int, @pro varchar(100) ) returns varchar(100) as begin declare @str varchar(100) set @str = '' select @str = @str + ',' + cast(id as varchar) from test where did = @col and ',' + @pro + ',' like '%,'+ cast(id as varchar) +',%' return stuff(@str,1,1,'') end--声明参数变量 declare @pro varchar(100) set @pro = '68,80,81'--查询 select distinct did,dbo.f_connstr(did,@pro) as id from test--结果 69 80,81 70 68--删除环境 drop function f_connstr drop table test
id 表示小鼠的编号 dqfzlid 表示繁殖笼号 传递id 68,80,81 也就是传递了3只小鼠得出繁殖笼的信息 dqfzlid id 69 80,81 70 68id只反映传递的小鼠 不需要繁殖笼里全部的
--函数
create function fn_Ids(
@dqfzlid int
)
returns varchar(200)
as
begin
declare @Ids varchar(200)
set @Ids=''
select @Ids=@Ids+','+rtrim(id) from tab where dqfzlid =@dqfzlid
if @Ids<>''
set @Ids=stuff(@Ids,1,1,'')
return @Ids
end
go--存储过程
create proc pr_Query
@Ids varchar(200)
as
exec('select dqfzlid,dbo.fn_Ids(dqfzlid) as Id from tab where id in ('+@Ids+') group by dqfzlid')
go--调用
exec pr_Query '68,80,81'
--环境
create table test
(
id int,
did int
)
insert into test select 68, 70
insert into test select 69, 70
insert into test select 70, 70
insert into test select 80, 69
insert into test select 81, 69 --建立辅助函数
create function f_connstr
(
@col int,
@pro varchar(100)
)
returns varchar(100)
as
begin
declare @str varchar(100)
set @str = ''
select @str = @str + ',' + cast(id as varchar)
from test
where did = @col and ',' + @pro + ',' like '%,'+ cast(id as varchar) +',%' return stuff(@str,1,1,'')
end--声明参数变量
declare @pro varchar(100)
set @pro = '68,80,81'--查询
select distinct did,dbo.f_connstr(did,@pro) as id
from test--结果
69 80,81
70 68--删除环境
drop function f_connstr
drop table test
也就是传递了3只小鼠得出繁殖笼的信息
dqfzlid id
69 80,81
70 68id只反映传递的小鼠 不需要繁殖笼里全部的