SQL Server自定义函数(Table-valued Functions)

最新推荐文章于 2024-05-28 11:34:53 发布

weixin_30933531

最新推荐文章于 2024-05-28 11:34:53 发布

阅读量186

点赞数

CC 4.0 BY-SA版权

文章标签：数据库

原文链接：http://www.cnblogs.com/insus/archive/2010/12/28/1918989.html

本文介绍了两种表值用户自定义函数(UDF)的创建方法。第一种直接插入固定值到返回表中，第二种通过连接多个表来生成动态结果集。

自定义函数(User-Defined Function)有两种，一种是标量UDF(Scalar-valued Functions)和表值UDF(Table-valued Functions)，前者只返回单个数据值，而后者则返回一个表。前面演示了标量自定义函数，http://www.cnblogs.com/insus/articles/1918983.html ，现在下面两个实例均演示表值自定义函数，

代码

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [ dbo ] . [ udf_SuperUser ]
(
)
RETURNS @SuperUser TABLE ( [ Account ] nvarchar ( 30 ), [ Password ] nvarchar ( 30 ))
AS
BEGIN
INSERT SuperUser ( [ Account ] , [ Password ] ) VALUES ( ' Admin ' , ' mypassword@2010 ' );
RETURN
END

另一个例子,

代码

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [ dbo ] . [ udf_Supervisor ]
(
)
RETURNS TABLE
AS
RETURN
(
     SELECT [ SupervisorId ] ,e. [ WorkNumber ] , [ EmployeeName ] ,w. [ WorkGroupId ] , [ WorkGroupName ] ,s. [ IsActive ]
     FROM [ Supervisor ] s
     LEFT JOIN [ Employee ] e ON (s. [ WorkNumber ] = e. [ WorkNumber ] )
     LEFT JOIN [ WorkGroup ] w ON (s. [ WorkGroupId ] = w. [ WorkGroupId ] )
)

转载于:https://www.cnblogs.com/insus/archive/2010/12/28/1918989.html