server sql 函数用法（包含三中函数）

--第一种标准函数

use Test
go

create function dbo.AvgHeight(@Sex varchar(10)='男')    --在这里是给参数默认值，即可以不传参数调用这个函数
returns decimal
as
begin
    declare @AvgHeight decimal(10,2)
    select @AvgHeight=AVG(Convert(decimal(10,2),S_Height)) from Student where S_Sex=@Sex
    return @AvgHeight
end
go

--返回固定值

--内嵌表值函数

use Test
go

create function dbo.SearchStuInfo(@Stu_No varchar(50)='001')
returns table
as
return(
select S_StuNo,S_Name,S_Sex,S_Height,S_BirthDate 
from Student where S_StuNo=@Stu_No)
go

--返回table

 

--多声明表值函数

use Test
go

create function dbo.SearchStuCou(@StuN varchar(10)='001')
returns @StuInfo table (
    StuNo varchar(50) not null primary key,
    StuName varchar(10) not null,
    StuSex  varchar(10) not null,
    StuHeight varchar(10) null,
    StuAvgHeight decimal(10,2) null,
    StuConAvgHeight varchar(30) null,
    StuCou varchar(10) null
)
as
begin
    declare @Height decimal(10,2)
    declare @AvgHeight decimal(10,2)
    declare @ConAvgHeight varchar(30)

    select @AvgHeight=AVG(Convert(decimal(10,2),S_Height))        --查询所有同学的平均身高
    from Student 

    select @Height=Convert(decimal(10,2),S_Height)        --查询指定学号的学生身高
    from Student 
    where S_StuNo=@StuN

    if((@Height-@AvgHeight) > Convert(decimal(10,2),0))
    begin
        set @ConAvgHeight='大于平均身高'
    end
    else if((@Height-@AvgHeight) = Convert(decimal(10,2),0))
    begin
        set @ConAvgHeight='等于平均身高'
    end
    else
    begin
        set @ConAvgHeight='小于平均身高'
    end

    insert into @StuInfo(StuNo,StuName,StuSex,StuHeight,StuAvgHeight,StuConAvgHeight,StuCou)
    select s.S_StuNo,s.S_Name,s.S_Sex,s.S_Height,@AvgHeight,@ConAvgHeight,c.C_Name from Student s 
    left join Course c on s.C_S_Id=c.C_Id
    where S_StuNo=@StuN 

    return
end
go