Pages

Friday 11 January 2013

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

No comments:

Post a Comment