Tuesday, November 15, 2016

User Defined Functions in SQL Server

A user-defined function is a Transact-SQL or common language runtime (CLR) routine that accepts parameters, performs an action, such as a complex calculation, and returns the result of that action as a value. The return value can either be a scalar (single) value or a table.

Use this statement to create a reusable routine that can be used in these ways:
·         In Transact-SQL statements such as SELECT
·         In applications calling the function
·         In the definition of another user-defined function
·         To parameterize a view or improve the functionality of an indexed view
·         To define a column in a table
·         To define a CHECK constraint on a column
·         To replace a stored procedure
·         Use an inline function as a filter predicate for a security policy

Example for scalar Function:-

The function which accepts one or more parameters and returns a Scalar/Single value.

--Transact-SQL Scalar Function Syntax 
CREATE FUNCTION dbo.test_function (@input int) --(input)
RETURNS int  -- what is this function retruning
AS 
BEGIN  
DECLARE @RET INT
SELECT @RET=
sum([ListPrice])FROM [AdventureWorks2008R2].[Production].[Product]
where productid=@input group by name
RETURN @RET --(output)
END 

To call function use below syntax

select dbo.test_function(809) As TotalListPrice

 Ex2-

CREATE FUNCTION ufn_GetFullName(@Bid int)
  RETURNS VARCHAR(30)
  AS
  BEGIN
  DECLARE @fullName VARCHAR(30)
  SELECT @fullName=
             [FirstName]+' '+[MiddleName]+' '+[LastName]
  FROM [AdventureWorks2008R2].[Person].[Person] where BusinessEntityID=@Bid
  RETURN @fullName
  END

SELECT  dbo.ufn_GetFullName(285)

SYNTAX:-
CREATE FUNCTION <FunctionName>(@Parameter <datatype>)
RETURN <DATA TYPE(SIZE)>
AS
BEGIN
DECLARE @RETURN <DATA TYPE>
FUNCTION BODY
RETURN @RETURN
END

Table-Valued Functions
The function takes one input parameter, a customer (store) ID, and returns the columns ProductID, Name, and the aggregate of year-to-date sales as YTD Total for each product sold to the store.

It will return table values

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

SELECT * FROM Sales.ufn_SalesByStore(602);
 

Creating a multi-statement table-valued function

The following example creates the table-valued function fn_FindReports(InEmpID) in the AdventureWorks2012 database. When supplied with a valid employee ID, the function returns a table that corresponds to all the employees that report to the employee either directly or indirectly. The function uses a recursive common table expression (CTE) to produce the hierarchical list of employees. For more information about recursive CTEs, see WITH common_table_expression (Transact-SQL).
 
IF OBJECT_ID (N'dbo.ufn_FindReports', N'TF') IS NOT NULL 
    DROP FUNCTION dbo.ufn_FindReports; 

GO 

CREATE FUNCTION dbo.ufn_FindReports (@InEmpID INTEGER) 
RETURNS @retFindReports TABLE  
    EmployeeID int primary key NOT NULL, 
    FirstName nvarchar(255) NOT NULL, 
    LastName nvarchar(255) NOT NULL, 
    JobTitle nvarchar(50) NOT NULL, 
    RecursionLevel int NOT NULL 

--Returns a result set that lists all the employees who report to the  
--specific employee directly or indirectly.*/ 

AS 
BEGIN 

WITH EMP_cte(EmployeeID, OrganizationNode, FirstName, LastName, JobTitle, RecursionLevel) -- CTE name and columns 
    AS ( 
        SELECT e.BusinessEntityID, e.OrganizationNode, p.FirstName, p.LastName, e.JobTitle, 0 -- Get the initial list of Employees for Manager n 
        FROM HumanResources.Employee e  
INNER JOIN Person.Person p  
ON p.BusinessEntityID = e.BusinessEntityID 
        WHERE e.BusinessEntityID = @InEmpID 
        UNION ALL 
        SELECT e.BusinessEntityID, e.OrganizationNode, p.FirstName, p.LastName, e.JobTitle, RecursionLevel + 1 -- Join recursive member to anchor 
        FROM HumanResources.Employee e  
            INNER JOIN EMP_cte 
            ON e.OrganizationNode.GetAncestor(1) = EMP_cte.OrganizationNode 
INNER JOIN Person.Person p  
ON p.BusinessEntityID = e.BusinessEntityID) 

-- copy the required columns to the result of the function  

   INSERT @retFindReports 
   SELECT EmployeeID, FirstName, LastName, JobTitle, RecursionLevel 
   FROM EMP_cte  
   RETURN 
END; 
GO 

-- Example invocation 
SELECT EmployeeID, FirstName, LastName, JobTitle, RecursionLevel 

FROM dbo.ufn_FindReports(1);  
  
GO 
 

Limitations and restrictions

·         User-defined functions cannot be used to perform actions that modify the database state.
·         User-defined functions cannot contain an OUTPUT INTO clause that has a table as its target.
·         User-defined functions cannot return multiple result sets. Use a stored procedure if you need to return multiple result sets.
·         Error handling is restricted in a user-defined function. A UDF does not support TRY…CATCH, @ERROR or RAISERROR.
·         User-defined functions cannot call a stored procedure, but can call an extended stored procedure.
·         User-defined functions cannot make use of dynamic SQL or temp tables. Table variables are allowed.
·         SET statements are not allowed in a user-defined function.
·         The FOR XML clause is not allowed
·         User-defined functions can be nested; that is, one user-defined function can call another. The nesting level is incremented when the called function starts execution, and decremented when the called function finishes execution. User-defined functions can be nested up to 32 levels. Exceeding the maximum levels of nesting causes the whole calling function chain to fail. Any reference to managed code from a Transact-SQL user-defined function counts as one level against the 32-level nesting limit. Methods invoked from within managed code do not count against this limit.
·         The following Service Broker statements cannot be included in the definition of a Transact-SQL user-defined function:
o    BEGIN DIALOG CONVERSATION
o    END CONVERSATION
o    GET CONVERSATION GROUP
o    MOVE CONVERSATION
o    RECEIVE
o    SEND

 

Permissions

Requires CREATE FUNCTION permission in the database and ALTER permission on the schema in which the function is being created. If the function specifies a user-defined type, requires EXECUTE permission on the type.