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=[Link](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
Here is the function call. Notice that DATEFIRST is set to 1.
SET DATEFIRST 1 SELECT [Link]('12/26/1999') AS 'ISO Week'
Here is the result set.
ISO Week ---------------52
B. Inline table-valued function
This example returns an inline table-valued function.
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)
C. Multi-statement table-valued function
Given a table that represents a hierarchical relationship:
CREATE TABLE employees (empid nchar(5) PRIMARY KEY, empname nvarchar(50), mgrid nchar(5) REFERENCES employees(empid), title nvarchar(30) )
The table-valued function fn_FindReports(InEmpID), which -- given an Employee ID -- returns a table corresponding to all the employees that report to the given employee directly or indirectly. This logic is not expressible in a single query and is a good candidate for implementing as a user-defined function.
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 [Link], [Link], [Link], [Link], 0 FROM employees e, @reports r WHERE [Link]=[Link] and [Link] <> [Link] and [Link] = 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 See Also
ALTER FUNCTION DROP FUNCTION Invoking User-defined Functions User-defined Functions 1988-2000 Microsoft Corporation. All Rights Reserved.