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 返回类型

AS
BEGIN
	SQL语句
	RETURN;
END

下面是一个例子：

--创建标量函数，返回两个数之间的较大者
CREATE FUNCTION dbo.F3_GetMax
(
	@a INT,   --函数的两个参数
	@b INT
)
RETURNS INT --返回类型为INT
AS
BEGIN
	DECLARE @max INT;
	IF(@a>=@b)
	BEGIN
		SET @max = @a;
	END
	ELSE
	BEGIN
		SET @max = @b
	END

	RETURN @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 TABLE
AS
RETURN

(
	一条sql查询语句
)

下面是一个例子

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

注意：

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

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

（2）多语句表值函数

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

CREATE FUNCTION 函数名（参数）
RETURNS 表变量名称（表变量字段定义）
AS
BEGIN
	SQL语句
	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)
)
AS
BEGIN
 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语句返回表变量
END

SELECT * FROM dbo.GetEmployeeSalary('1','aa')

注意：

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

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

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