SQL Server Functions

SQL Server Functions

SQL Server Functions

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()

Example code is taken from the MSDN website

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;
GO
CREATE FUNCTION dbo.ISOweek (@DATE datetime)
RETURNS int
WITH EXECUTE AS CALLER
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;
GO
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;
GO
CREATE FUNCTION Sales.ufn_SalesByStore (@storeid int)
RETURNS TABLE
AS
RETURN
(
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.

Leave a Reply

Your email address will not be published. Required fields are marked *