SQL SERVER 2005 UDF

最新推荐文章于 2025-04-25 23:22:25 发布

原创最新推荐文章于 2025-04-25 23:22:25 发布 · 707 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#sql server #function #date #object #email #null

SQL2005学习日志专栏收录该内容

17 篇文章

订阅专栏

本文介绍在SQL Server中创建及使用自定义标量函数和表值函数的方法。包括如何创建返回日期部分的标量函数，以及创建返回联系人列表和基于姓氏搜索联系人的表值函数。此外，还展示了递归表值函数的应用，用于检索员工及其下属的信息。

use AdventureWorks CREATE FUNCTION dbo.DayOnly( @Date datetime ) RETURNS VARCHAR(12) AS BEGIN RETURN CONVERT(VARCHAR(12),@Date,101) END SELECT OBJECTPROPERTY(OBJECT_ID('DayOnly'),'IsDeterministic') SELECT dbo.DayOnly(GETDATE()) ALTER FUNCTION dbo.DayOnly( @Date datetime ) RETURNS VARCHAR(12) WITH SCHEMABINDING AS BEGIN RETURN CONVERT(VARCHAR(12),@Date,101) END SELECT OBJECTPROPERTY(OBJECT_ID('DayOnly'),'IsDeterministic') SELECT dbo.DayOnly(GETDATE()) CREATE FUNCTION dbo.fnContactList() RETURNS TABLE AS RETURN (SELECT ContactID,LastName + ', ' + FirstName AS Name, EMailAddress AS email FROM Person.Contact) SELECT * FROM fnContactList() CREATE FUNCTION dbo.fnContactSearch(@LastName nvarchar(50)) RETURNS TABLE AS RETURN (SELECT ContactID,LastName + ', ' + FirstName AS Name, EMailAddress AS email FROM Person.Contact WHERE LastName LIKE @LastName + '%') SELECT * FROM fnContactSearch('Ad') ALTER FUNCTION dbo.fnGetReports( @EmployeeID as int ) RETURNS @Reports TABLE ( EmployeeID int not null, ManagerID int null ) AS BEGIN DECLARE @Employee AS INT INSERT INTO @Reports SELECT EmployeeID,ManagerID FROM HumanResources.Employee WHERE EmployeeID = @EmployeeID SELECT @Employee = MIN(EmployeeID) FROM HumanResources.Employee WHERE ManagerID = @EmployeeID WHILE @EmployeeID IS NOT NULL BEGIN INSERT INTO @Reports SELECT * FROM fnGetReports(@EmployeeID) SELECT @Employee = MIN(EmployeeID) FROM HumanResources.Employee WHERE EmployeeID > @Employee AND ManagerID = @EmployeeID END RETURN END GO SELECT * FROM fnGetReports(12)