insert t
select 1,'小王',28 union all
select 2,'小李',24 union all
select 3,'小张',26 union all
select 4,'小周',27
go
create function f_he(@col1 int,@col2 int)
returns varchar(50)
as
begin
if @col1>@col2
return(null)
declare @sql varchar(50)
set @sql=''
select @sql=@sql+','+name from t where age>@col1
return(stuff(@sql,1,1,''))
end
go
select *,dbo.f_he(25,age) as newfield from t
drop function dbo.f_he
drop table t
id name age newfield
----------- -------------------- ----------- --------------------------------------------------
1 小王 28 小王,小张,小周
2 小李 24 NULL
3 小张 26 小王,小张,小周
4 小周 27 小王,小张,小周
insert t
select 1,'小王',28 union all
select 2,'小李',24 union all
select 3,'小张',26 union all
select 4,'小周',27
go
create function f_he(@col int)
returns @t table(col varchar(50))
as
begin
declare @sql varchar(50)
set @sql=''
select @sql=@sql+','+name from t where age>@col
insert @t values (stuff(@sql,1,1,''))
return
end
go
select * from dbo.f_he(25)
drop function dbo.f_he
drop table t
col
--------------------------------------------------
小王,小张,小周