Explain User Defined Functions in Sql Server

User-defined functions allow you to encapsulate both logic and subroutines into a single function that can then be used within your Transact-SQL queries and programmatic objects. User-defined types allow you to create an alias type based on an underlying system data type, enforcing a specific data type, length, and nullability.

UDF Basics

Transact-SQL user-defined functions fall into three categories;

• scalar values
• inline table-valued
• multi-statement table-valued

. A scalar user-defined function is used to return a single value based on zero or more parameters. For example, you could create a scalar UDF that accepts a CountryID as a parameter, and returns the CountryNM.
An inline table-valued UDF returns a table data type based on a single SELECT statement that is used to define the returned rows and columns. Unlike a stored procedure, an inline UDF can be referenced in the FROM clause of a query, as well as be joined to other tables. Unlike a view, an inline UDF can accept parameters.
A multi-statement table-valued UDF also returns a result set and is referenced in the FROM clause. Unlike inline table-valued UDFs, they aren’t constrained to use a single SELECT statement within the function definition and instead allow multiple Transact-SQL statements in the body of the UDF definition in order to define a single, final result set to be returned.
Like stored procedures, UDFs can perform well because their query execution plans are cached for reuse. UDFs can also be used in places where a stored procedure can’t, like in the FROM and SELECT clause of a query. UDFs also encourage code reusability. For example, if you create a scalar UDF that returns the CountryNM based on a CountryID, and the same function is needed across several different stored procedures, rather than repeat the 20 lines of code needed to perform the lookup, you can call the UDF function instead.

the Syntax of Scalar User-Defined Functions is as follows:

CREATE FUNCTION FunctionName (InputParameters)
RETURNS DataType
AS
BEGIN
Code
RETURN Expression
END

Example Creating Scalar User-Defined Functions

CREATE FUNCTION dbo.Multiply (@A INT, @B INT = 3)
RETURNS INT
AS
BEGIN
RETURN @A * @B
End
SELECT dbo.Multiply (3,4)
SELECT dbo.Multiply (7, DEFAULT)

Result:

12

21

Inline Table-Valued Functions

The second type of user-defined function is very similar to a view. Both are wrapped for a stored select statement. An inline table-valued user-defined function retains the benefits of a view, and adds compilation and parameters. As with a view, if the select statement is updateable, then the function will be updateable.

Creating an Inline Table-Valued Function

The inline table-valued user-defined function has no begin/end body. Instead, the select statement is returned as a table data type:

CREATE FUNCTION FunctionName (InputParameters)
RETURNS Table
AS
RETURN (Select Statement)

The following inline table-valued user-defined function is functionally equivalent to the vEventList view created in Chapter 4, “Creating Views.”

CREATE FUNCTION fEventList ()
RETURNS Table
AS
RETURN(
SELECT dbo.CustomerType.Name AS Customer,
dbo.Customer.LastName, dbo.Customer.FirstName,
dbo.Customer.Nickname,
dbo.Event_mm_Customer.ConfirmDate, dbo.Event.Code,
dbo.Event.DateBegin, dbo.Tour.Name AS Tour,
dbo.BaseCamp.Name, dbo.Event.Comment
FROM dbo.Tour
INNER JOIN dbo.Event
ON dbo.Tour.TourID = dbo.Event.TourID
INNER JOIN dbo.Event_mm_Customer
ON dbo.Event.EventID = dbo.Event_mm_Customer.EventID
INNER JOIN dbo.Customer
ON dbo.Event_mm_Customer.CustomerID
= dbo.Customer.CustomerID
LEFT OUTER JOIN dbo.CustomerType
ON dbo.Customer.CustomerTypeID
= dbo.CustomerType.CustomerTypeID
INNER JOIN dbo.BaseCamp
ON dbo.Tour.BaseCampID = dbo.BaseCamp.BaseCampID
)

Calling an Inline Table-Valued Function

To retrieve data through fEventList, call the function within the from portion of a select statement:

SELECT LastName, Code, DateBegin
FROM dbo.fEventList()

Result :

LastName	Code	DateBegin
Anderson	01-003	2001-03-16 00:00:00.000
Brown	01-003	2001-03-16 00:00:00.000
Frank	01-003	2001-03-16 00:00:00.000

Multi-Statement Table-Valued Functions

The multi-statement table-valued user-defined function combines the scalar function’s ability to contain complex code with the inline table-valued function’s ability to return a result set. This type of function creates a table variable and then populates it within code. The table is then passed back from the function so that it may be used within select statements. The primary benefit of the multi-statement table-valued user-defined function is that complex result sets may be generated within code and then easily used with a select statement. This enables these functions to be used in place of stored procedures that return result sets. The new apply command may be used with multi-statement user-defined functions in the same way it’s used with inline user-defined functions.

Creating a Multi-Statement Table-Valued Function

The syntax to create the multi-statement table-valued function is very similar to that of the scalar user-defined function:

CREATE FUNCTION FunctionName (InputParamenters)
RETURNS @TableName TABLE (Columns)
AS
BEGIN
Code to populate table variable
RETURN
END

Create Function

CREATE FUNCTION fPriceAvg()
RETURNS @Price TABLE
(Code CHAR(10),
EffectiveDate DATETIME,
BEGIN
INSERT @Price (Code, EffectiveDate, Price)
SELECT Code, EffectiveDate, Price
FROM Product
JOIN Price ON Price.ProductID = Product.ProductID
INSERT @Price (Code, EffectiveDate, Price)
SELECT Code, Null, Avg(Price)
FROM Product
JOIN Price
ON Price.ProductID = Product.ProductID
GROUP BY Code
RETURN
END

Calling the Function

To execute the function, refer to it within the from portion of a select statement. The following code retrieves the result from the fPriceAvg function:

SELECT * FROM dbo.fPriceAvg()

Result:

Code	EffectiveDate	Price
1001	2001-05-01 00:00:00.000 14.9500	14.9500
1001	2002-06-01 00:00:00.000 15.9500	15.9500
1001	2002-07-20 00:00:00.000	17.9500