以下为引用的内容：CREATE FUNCTION ISOweek (@DATE datetime)RETURNS intASBEGINDECLARE @ISOweek intSET @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 yearIF (@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 yearIF ((DATEPART(mm,@DATE)=12) AND ((DATEPART(dd,@DATE)-DATEPART(dw,@DATE))>= 28))SET @ISOweek=1RETURN(@ISOweek)END
下面是函数调用。注意 DATEFIRST 设置为 1。 站.长.站
以下为引用的内容：SET DATEFIRST 1SELECT master.dbo.ISOweek('12/26/1999') AS 'ISO Week' 站.长.站
下面是结果集。 中国.站.长站
以下为引用的内容：ISO Week----------------52 站.长站
B. 内嵌表值函数 站.长.站
下例返回内嵌表值函数。
以下为引用的内容：USE pubsGOCREATE FUNCTION SalesByStore (@storeid varchar(30))RETURNS TABLEASRETURN (SELECT title, qtyFROM sales s, titles tWHERE s.stor_id = @storeid andt.title_id = s.title_id) 中国站.长.站
C. 多语句表值函数 Www~~com
假设有一个表代表如下的层次关系： 中.国站长站
以下为引用的内容：CREATE TABLE employees (empid nchar(5) PRIMARY KEY, empname nvarchar(50), mgrid nchar(5) REFERENCES employees(empid), title nvarchar(30)) Www~~com
表值函数 fn_FindReports(InEmpID) 有一个给定的职员ID，它返回与所有直接或间接向给定职员报告的职员相对应的表。 Www..com
该逻辑无法在单个查询中表现出来，不过可以实现为用户定义函数。 中.国.站.长.站
以下为引用的内容：
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.*/ASBEGINDECLARE @RowsAdded int-- table variable to hold accumulated resultsDECLARE @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 @reportsSELECT empid, empname, mgrid, title, 0FROM employees WHERE empid = @InEmpId SET @RowsAdded = @@rowcount-- While new employees were added in the previous iterationWHILE @RowsAdded > 0BEGIN/*Mark all employee records whose direct reports are going to be found in this iteration with processed=1.*/UPDATE @reportsSET processed = 1WHERE processed = 0-- Insert employees who report to employees marked 1.INSERT @reportsSELECT e.empid, e.empname, e.mgrid, e.title, 0FROM employees e, @reports rWHERE e.mgrid=r.empid and e.mgrid <> e.empid and r.processed = 1SET @RowsAdded = @@rowcount/*Mark all employee records whose direct reports have been foundin this iteration.*/UPDATE @reportsSET processed = 2WHERE processed = 1END Www..com
-- copy to the result of the function the required columnsINSERT @retFindReportsSELECT empid, empname, mgrid, title FROM @reportsRETURNENDGO 站.长站
-- Example invocationSELECT * FROM fn_FindReports('11234')GO Www__com
Www~~com
文章整理：西部数码--专业提供域名注册、虚拟主机服务
http://www.west263.com
以上信息与文章正文是不可分割的一部分,如果您要转载本文章,请保留以上信息，谢谢!
