Sometimes you read books on subjects about SQL Server and you just want to learn the basics, well here are the basics of SQL Server Functions
Functions can be Scalar or Table-valued
Scalar returns one value and Table-valued functions (TVF) returns…well a table of results and this are usually found in the FROM clause of a statement.
Functions can be either Deterministic or Nondeterministic
Deterministic = This means they return the same value any time they are called with a specific set of input values.
i.e. SELECT LEN(‘TEST) will always return 4
Nondeterministic = different results every time
i.e SELECT GETDATE()
The following example creates the user-defined function (Scalar) ISOweek. This function takes a date argument and calculates the ISO week number. For this function to calculate correctly, SET DATEFIRST 1 must be invoked before the function is called.
IF OBJECT_ID (N’dbo.ISOweek’, N’FN’) IS NOT NULL
DROP FUNCTION dbo.ISOweek;
CREATE FUNCTION dbo.ISOweek (@DATE datetime)
WITH EXECUTE AS CALLER
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
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
SET DATEFIRST 1;
SELECT dbo.ISOweek(CONVERT(DATETIME,’12/26/2004′,101)) AS ‘ISO Week’;
The following example returns an inline table-valued function.
It returns three columns ProductID, Name and the aggregate of
year-to-date totals by the store as YTD Total for each product sold to the store.
IF OBJECT_ID (N’Sales.ufn_SalesByStore’, N’IF’) IS NOT NULL
DROP FUNCTION Sales.ufn_SalesByStore;
CREATE FUNCTION Sales.ufn_SalesByStore (@storeid int)
SELECT P.ProductID, P.Name, SUM(SD.LineTotal) AS ‘Total’
FROM Production.Product AS P
JOIN Sales.SalesOrderDetail AS SD ON SD.ProductID = P.ProductID
JOIN Sales.SalesOrderHeader AS SH ON SH.SalesOrderID = SD.SalesOrderID
JOIN Sales.Customer AS C ON SH.CustomerID = C.CustomerID
WHERE C.StoreID = @storeid
GROUP BY P.ProductID, P.Name
That is a quick review of SQL Functions, if you have any questions, just leave a comment below.