在用存储过程操作数据库中的数据时,往往会用到函数,以便在处理过程时更加方便、合理,还可以重复利用函数简化代码量
自定义函数有两种:表值函数:返回一个表
标量值函数:返回一个标量值
具体函数结构如下:
<1>表值函数
create functiondbo.funTblTest[注:函数名]
(
[注:参数]
)
returns @tmpTable[注:表格变量名] table
(
[注:表格变量定义]
)
as
begin
[注:sql语句]
end
实例讲析:通过部门Id查找其子级Id,可以是多级,并返回数据到表中
create function funTbTest
(
@departId int
)
returns @tmpTbl table
(
rowId int identity primary key,
departId int,
departPid int,
departName varchar(50)
)
as
begin
declare @departPid int
declare @departName varchar(50)
select @departPid=departPid from TdepartmentInfo where departId=@departId
select @departName=departName from TdepartmentInfo wheredepartId=@departId
insert into @tmpTbl(departId,departPid,departName)values(departId,@departPid,@departName)
while exists(
select departId,departPid,departName fromTdepartmentInfo where departPid in
(select departId from @tmpTbl ) and departId not in(select departId from @tmpTbl)
)
begin
insert into @tmpTbl select departId,departPid,departName from TdepartmentInfo where departPid in (select departId from @tmpTbl )
end
return
end
调用:select * from funTbTest(12)
<2>标量值函数
create function[函数名]
(
[参数]
)
returns [标量值类型]
as
begin
[注:sql语句]
end
实例讲析:通过部门Id查找其子级Id,可以是多级,并返回数据到表中
create function funStrTest
(
@depart_id int
)
returns int
as
begin
declare @tmpRtn int
select @tmpRtn=count(*)from TdepartmentInfo wheredepartPid=@depart_id
return @tmpRtn
end
调用: select dbo.funStrTest(2) 注:dbo.一定不要忘记写 不然是调用不到函数的