例子:--改写liangCK的部分代码为函数
--创建函数
create function generateTime
(
@begin_date datetime,
@end_date datetime
)
returns @t table(date datetime)
as
begin
with maco as
(
select @begin_date AS date
union all
select date+1 from maco
where date+1 <=@end_date
)
insert into @t
select * from maco option(maxrecursion 0);
return
end
go
--测试示例
select * from dbo.generateTime('2009-01-01','2009-01-10')
--运行结果
/*
date
-----------------------
2009-01-01 00:00:00.000
2009-01-02 00:00:00.000
2009-01-03 00:00:00.000
2009-01-04 00:00:00.000
2009-01-05 00:00:00.000
2009-01-06 00:00:00.000
2009-01-07 00:00:00.000
2009-01-08 00:00:00.000
2009-01-09 00:00:00.000
2009-01-10 00:00:00.000
*/
--创建函数
create function generateTime
(
@begin_date datetime,
@end_date datetime
)
returns @t table(date datetime)
as
begin
with maco as
(
select @begin_date AS date
union all
select date+1 from maco
where date+1 <=@end_date
)
insert into @t
select * from maco option(maxrecursion 0);
return
end
go
--测试示例
select * from dbo.generateTime('2009-01-01','2009-01-10')
--运行结果
/*
date
-----------------------
2009-01-01 00:00:00.000
2009-01-02 00:00:00.000
2009-01-03 00:00:00.000
2009-01-04 00:00:00.000
2009-01-05 00:00:00.000
2009-01-06 00:00:00.000
2009-01-07 00:00:00.000
2009-01-08 00:00:00.000
2009-01-09 00:00:00.000
2009-01-10 00:00:00.000
*/
--创建多语句表值函数
create function dbo.splitStr(@str varchar(300),@split varchar(10))
returns @t table(v varchar(50))
as
begin
declare @temp varchar(50);
set @temp = '';
while charindex(@split,@str) > 0
begin
set @temp = LEFT(@str,CHARINDEX(@split,@str) - 1 );
insert into @t(v)
values(@temp);
set @str = STUFF(@str,1,charindex(@split,@str),'')
end
insert into @t(v)
values(@str)
return; --返回
end
go
--使用表值函数
select v
from dbo.splitStr('a,b,c,d',',') t
/*
v
a
b
c
d
*/
create table dbo.t
(id int not null primary key clustered,
mStr varchar(300)
)insert into dbo.t(id)
values(1),
(2),
(3)
go--内嵌表值函数,只包含一个return,不能包含其他语句
--注意:各视图或函数中的列名必须唯一。
create function dbo.display(@id int )
returns table
asreturn(
select *
from t
)
go
--使用内联表值函数
select *
from dbo.display(1)