sql server表值函数和标量函数

在用存储过程操作数据库中的数据时，往往会用到函数，以便在处理过程时更加方便、合理，还可以重复利用函数简化代码量

自定义函数有两种：表值函数：返回一个表

标量值函数：返回一个标量值

具体函数结构如下：

<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.一定不要忘记写 不然是调用不到函数的

USE [DBNAME]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <>
-- Create date: <>
-- Description: <>
-- =============================================

ALTER FUNCTION [dbo].[fn_NAME] (@PID INT)
RETURNS varchar(MAX)
AS
BEGIN
DECLARE @rtStr varchar(MAX)
SET @rtStr=''

SELECT @rtStr = @rtStr + ISNULL(ColumnName,'')+ISNULL(ExtValue,'') + ' ' FROM TABLENAME1 cfg INNER JOIN TABLENAME2 ext
ON cfg.ConfigID=ext.ConfigID
WHERE PID =@PID

RETURN @rtStr
END