sql server 2008之自定义函数—标量值函数使用

1、创建标量函数-面积计算

use hotelmanagementsys

go

create function area(@width int , @height int)

returns int

as 

begin

return @width * @height

end

2、标量函数引用-面积计算

use hotelmanagementsys

go

select dbo.area(4 , 7) as '面积' 

3、创建标量函数-房间入住信息的表

use hotelmanagementsys

go

create table 房间入住信息(

gname varchar(50),

gno varchar(50),

rno varchar(50),

rtype varchar(50)

)

4、创建标量函数-查询顾客入住信息

USE hotelmanagementsys

GO

create function getroomtypebyname(@name char(50))

returns varchar(10)

as

begin

declare @gno varchar(50) , @rno varchar(50) , @result varchar(50) = '暂无人居住信息'

set @gno = select gno from 房间入住信息 where gname = @name

set @rno = select rno from 房间入住信息 where gno = @gno

set @result = select rtype from 房间入住信息 where rno = @rno

return @result

end

5、标量函数引用-查询顾客入住信息

use hotelmanagementsys

go

select dbo.getroomtypebyname('琳琳') as '琳琳入住房间信息'