sqlserver自定义函数（标量值函数，表值函数）

在sqlserver中，根据函数返回值形式的不同将用户自定义的函数分为”标量值函数“和”表值函数“两种类型。如下图所示，可以在数据库中的可编程->函数中进行查看。

标量函数，返回单个值，表值函数返回表数据

1.标量值函数

标量值函数返回一个确定类型的标量值，其返回类型除text、ntext、image、cursor、timestamp、和table类型外的其他数据类型，函数体语句定义在begin-end内部。在returns（注意有带s）语句后定义返回值的数据类型，并且函数的最后一条语句必须为return，（注意没带s），其格式如下

<strong></strong><pre name="code" class="sql">CREATE FUNCTION 函数名（参数）RETURNS 返回类型

ASBEGINSQL语句RETURN;END

下面是一个例子：

--创建标量函数，返回两个数之间的较大者CREATE FUNCTION dbo.F3_GetMax(@a INT,   --函数的两个参数@b INT)RETURNS INT --返回类型为INTASBEGINDECLARE @max INT;IF(@a>=@b)BEGINSET @max = @a;ENDELSEBEGINSET @max = @bENDRETURN @max;  --最后return返回@max中的值END--调用函数SELECT dbo.F3_GetMax(2,3);

注意：

（1）必须使用两部分组成函数的名字来调用函数，即架构.对象名，如dbo.F3_GetMax(2,3)

（2）所有的传入参数前必须加@

（3）不要写漏和写错关键字，如as，returns，return

（4）returns后面不是跟一个变量，而是跟变量的返回值类型

（5）在begin/end语句块中，使用的是return

2.表值函数

表值函数有分为：内联表值函数和多语句表值函数

（1）内联表值函数

内联表值函数以表的形式进行返回，函数体不需要像标量值表值函数一样使用begin/end进行包围，其返回值是由一个位于return子句中的select命令从数据库中筛选出来，格式如下

<strong></strong><pre name="code" class="sql">CREATE FUNCTION 函数名称（参数）RETURNS TABLEASRETURN

(一条sql查询语句)

下面是一个例子

CREATE FUNCTION F3_GetEmployeeSalary(@EmployeeID VARCHAR(50) --参数)RETURNS TABLE  --返回类型为表ASRETURN(SELECT * FROM dbo.TEmployeeWHERE EmployeeID = @EmployeeID   --通过一条sql查询语句获取表中数据)--函数调用SELECT * FROM F3_GetEmployeeSalary('1')

注意：

1）只能返回Table，returns后边一定是table

2）as后面没有begin/end，只有一个return语句返回特定的记录

（2）多语句表值函数

多语句表值函数可以看做标量值函数与内联表值函数的结合体，其格式如下

CREATE FUNCTION 函数名（参数）RETURNS 表变量名称（表变量字段定义）ASBEGINSQL语句RETURN;END

例子如下

CREATE FUNCTION dbo.GetEmployeeSalary( @EmployeeID VARCHAR(50), @EmployeeName VARCHAR(50))RETURNS @table TABLE  --定义表变量，用于返回结果集数据( EmployeeID VARCHAR(50), EmployeeName VARCHAR(50), Salary INT, NowTime VARCHAR(50))ASBEGIN WITH CTEEmployeeSalary AS ( SELECT  EmployeeName, EmployeeID, Salary, GETDATE() AS timeNow FROM dbo.TEmployee WHERE EmployeeID = @EmployeeID AND EmployeeName = @EmployeeName ) INSERT INTO @table SELECT EmployeeName,EmployeeID,Salary,timeNow FROM CTEEmployeeSalary RETURN;  --return语句返回表变量ENDSELECT * FROM dbo.GetEmployeeSalary('1','aa')

注意：

1）returns后面直接定义返回的表类型，首先是定义表名，表明前面要加@，然后是关键字TABLE，最后是表的结构。(实质上是定义了一个表变量)

2） 在begin/end语句块中，直接将需要返回的结果insert到returns定义的表中就可以了，在最后return时，会将结果返回。

3） 最后只需要return，return后面不跟任何变量。(返回的是表变量里面的数据)