函数

create function MaxVal (@a int, @b int) returns int as
begin
 if(@a > @b)
 begin
  return @a;
 end
 return @b;
end
go

select dbo.MaxVal(3,5);
go

print dbo.MaxVal(3,7);
go

declare @maxV int;
set @maxV = dbo.MaxVal(12,dbo.MaxVal(7,9));
print @maxV;
go

----创建存储过程，根据给定的学号，取得姓名和总成绩
create procedure GetNameAndScore @stuId varchar(10), @stuName nvarchar(20) output, @totalScore float output as
begin
 select @totalScore = Sum(b.SCORE) from TB_SCORES as b
  inner join TB_STUDENTS as a on a.STU_KEY=b.STU_KEY
 where a.stu_id = @stuId;

 select @stuName = STU_NAME from tb_students where STU_ID=@stuId;
end

go

declare @stuName nvarchar(20);
declare @totalScore float;

execute GetNameAndScore '20080103', @stuName output, @totalScore output;
print '姓名:' + @stuName + ' 总成绩:' + convert(nvarchar, @totalScore);
go

--使用标量函数实现根据给定的学号取得总成绩
create function GetTotalScore(@stuId varchar(10)) returns float as
begin
 declare @totalScore float;
 select @totalScore = Sum(b.SCORE) from TB_SCORES as b
  inner join TB_STUDENTS as a on a.STU_KEY=b.STU_KEY
 where a.stu_id = @stuId;

 return @totalScore;
end;
go

select dbo.GetTotalScore('20080103');
go

print dbo.GetTotalScore('20080103');
go

create view vw_highscores as
select tb_students.stu_id as 学号, tb_students.stu_name as 姓名, dbo.GetTotalScore(stu_id) as 总成绩
from tb_students;

go

select * from vw_highscores;
go

--创建内联表值函数，根据指定的学号返回姓名和总成绩
create function GetNameAndTotalScore(@stuId varchar(10)) returns TABLE as
return select a.STU_NAME as 姓名, b.totalScore as 总成绩 from TB_STUDENTS as a
 inner join
 (select STU_KEY, Sum(SCORE) as totalScore from TB_SCORES group by STU_KEY) as b
 on a.STU_KEY=b.STU_KEY
 where a.STU_ID=@stuId;
 
go

select * from dbo.GetNameAndTotalScore('20080102');
go

--创建多语句表值函数，根据指定的学号返回姓名和总成绩
create function GetNameAndTotalScore_2(@stuId varchar(10))
 returns @tbl TABLE (姓名 nvarchar(20) not null, 成绩 float not null) as
begin
 declare @totalScore float;
 declare @stuName nvarchar(20);
 
 select @totalScore = Sum(b.SCORE) from TB_SCORES as b
  inner join TB_STUDENTS as a on a.STU_KEY=b.STU_KEY
 where a.stu_id = @stuId;

 if(@totalScore is null)
 begin
  return;
 end

 select @stuName = STU_NAME from tb_students where STU_ID=@stuId;

 insert into @tbl (姓名,成绩) values (@stuName,@totalScore);
 return;
end

go
select * from GetNameAndTotalScore_2('20080102');