示例 A. 计算 ISO 周的标量值用户定义函数 下例中，用户定义函数 ISOweek 取日期参数并计算 ISO 周数。为了正确计算该函数，必须在调用该函数前唤醒调用 SET DATEFIRST 1。
CREATE FUNCTION ISOweek (@DATE datetime) RETURNS int AS BEGIN DECLARE @ISOweek int SET @ISOweek= DATEPART(wk,@DATE) 1 -DATEPART(wk,CAST(DATEPART(yy,@DATE) as CHAR(4)) ‘0104’) –Special cases: Jan 1-3 may belong to the previous year IF (@ISOweek=0) SET @ISOweek=dbo.ISOweek(CAST(DATEPART(yy,@DATE)-1 AS CHAR(4)) ’12’ CAST(24 DATEPART(DAY,@DATE) AS CHAR(2))) 1 –Special case: Dec 29-31 may belong to the next year IF ((DATEPART(mm,@DATE)=12) AND ((DATEPART(dd,@DATE)-DATEPART(dw,@DATE))>= 28)) SET @ISOweek=1 RETURN(@ISOweek) END
下面是函数调用。注意 DATEFIRST 设置为 1。
SET DATEFIRST 1 SELECT master.dbo.ISOweek(’12/26/1999′) AS ‘ISO Week’ 下面是结果集。
ISO Week —————- 52
B. 内嵌表值函数 下例返回内嵌表值函数。
USE pubs GO CREATE FUNCTION SalesByStore (@storeid varchar(30)) RETURNS TABLE AS RETURN (SELECT title, qty FROM sales s, titles t WHERE s.stor_id = @storeid and t.title_id = s.title_id)
CREATE FUNCTION fn_FindReports (@InEmpId nchar(5)) RETURNS @retFindReports TABLE (empid nchar(5) primary key, empname nvarchar(50) NOT NULL, mgrid nchar(5), title nvarchar(30)) /*Returns a result set that lists all the employees who report to given employee directly or indirectly.*/ AS BEGIN DECLARE @RowsAdded int — table variable to hold accumulated results DECLARE @reports TABLE (empid nchar(5) primary key, empname nvarchar(50) NOT NULL, mgrid nchar(5), title nvarchar(30), processed tinyint default 0) — initialize @Reports with direct reports of the given employee INSERT @reports SELECT empid, empname, mgrid, title, 0 FROM employees WHERE empid = @InEmpId SET @RowsAdded = @@rowcount — While new employees were added in the previous iteration WHILE @RowsAdded > 0 BEGIN /*Mark all employee records whose direct reports are going to be found in this iteration with processed=1.*/ UPDATE @reports SET processed = 1 WHERE processed = 0 — Insert employees who report to employees marked 1. INSERT @reports SELECT e.empid, e.empname, e.mgrid, e.title, 0 FROM employees e, @reports r WHERE e.mgrid=r.empid and e.mgrid e.empid and r.processed = 1 SET @RowsAdded = @@rowcount /*Mark all employee records whose direct reports have been found in this iteration.*/ UPDATE @reports SET processed = 2 WHERE processed = 1 END
— copy to the result of the function the required columns INSERT @retFindReports SELECT empid, empname, mgrid, title FROM @reports RETURN END GO
— Example invocation SELECT * FROM fn_FindReports(‘11234’) GO
