表值函数和标值函数使用脚本范例

select * from authors

--多语句表值函数
use pubs
go
create function fuc_authors
()
returns @table_author_temp table
(
    au_id smallint,
    ai_lname varchar(40)
)
as
begin
    insert into @table_author_temp
        select au_id,au_lname from authors
    return
end

drop function fuc_authors


exec sp_helptext fuc_authors

alter function fuc_authors
(
    @fname varchar(20)='Dean'
)  
returns @table_author_temp table  
(  
    au_id varchar(40),  
    au_lname varchar(40)  
)  
as  
begin  
    insert into @table_author_temp  
        select au_id,au_lname from authors where au_fname=@fname 
    return  
end
go  

select * from fuc_authors(default)


--内联表值函数

create function inline_fuc_authors
(
    @fname varchar(20)='Dean'
)
returns table
as
return
(
    select au_id,au_lname from authors where au_fname=@fname
)
go

select * from dbo.inline_fuc_authors(default)

drop function inline_fuc_authors

--标量值函数
create function scalar_fuc_authors
(
    @fname varchar(40)='Dean'
)
returns varchar
as
begin
    declare @ret varchar(40)
    set @ret=(select au_id from authors where au_fname=@fname)
    return @ret
end
go

alter function scalar_fuc_authors
(
    @fname varchar(40)='Dean'
)
returns varchar(40)
as
begin
    declare @ret varchar(40)
    set @ret=(select au_id from authors where au_fname=@fname)
    return @ret
end
go

--此处执行一定要添加架构名‘dbo.’
select dbo.scalar_fuc_authors('White')