SQL Server自定义函数(Table-valued Functions)
自定义函数(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
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])
)
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])
)
