create table t
(id int,name varchar(20),age int)insert t
select 1,'小王',28 union all
select 2,'小李',24 union all
select 3,'小张',26 union all
select 4,'小周',27
gocreate 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 tcol
--------------------------------------------------
小王,小张,小周(所影响的行数为 1 行)
(id int,name varchar(20),age int)insert t
select 1,'小王',28 union all
select 2,'小李',24 union all
select 3,'小张',26 union all
select 4,'小周',27
gocreate 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 tcol
--------------------------------------------------
小王,小张,小周(所影响的行数为 1 行)
(id int,name varchar(20),age int)insert t
select 1,'小王',28 union all
select 2,'小李',24 union all
select 3,'小张',26 union all
select 4,'小周',27
gocreate 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 tdrop function dbo.f_he
drop table tid name age newfield
----------- -------------------- ----------- --------------------------------------------------
1 小王 28 小王,小张,小周
2 小李 24 NULL
3 小张 26 小王,小张,小周
4 小周 27 小王,小张,小周(所影响的行数为 4 行)