How To Create Function In Sql W3schools

Feedback
Edit

Create User-defined Functions (Database Engine)

12/22/2020
5 minutes to read

Applies to: SQL Server (all supported versions) Azure SQL Database

This topic describes how to create a user-defined function (UDF) in SQL Server by using Transact-SQL.

Before You Begin

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 can not 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:
- BEGIN DIALOG CONVERSATION
- END CONVERSATION
- GET CONVERSATION GROUP
- MOVE CONVERSATION
- RECEIVE
- 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.

Scalar Functions

The following example creates a multi-statement scalar function (scalar UDF) in the AdventureWorks2012 database. The function takes one input value, a ProductID, and returns a single data value, the aggregated quantity of the specified product in inventory.

            IF OBJECT_ID (N'dbo.ufnGetInventoryStock', N'FN') IS NOT NULL       DROP FUNCTION ufnGetInventoryStock;   GO   CREATE FUNCTION dbo.ufnGetInventoryStock(@ProductID int)   RETURNS int    AS    -- Returns the stock level for the product.   BEGIN       DECLARE @ret int;       SELECT @ret = SUM(p.Quantity)        FROM Production.ProductInventory p        WHERE p.ProductID = @ProductID            AND p.LocationID = '6';        IF (@ret IS NULL)            SET @ret = 0;       RETURN @ret;   END;

The following example uses the ufnGetInventoryStock function to return the current inventory quantity for products that have a ProductModelID between 75 and 80.

            SELECT ProductModelID, Name, dbo.ufnGetInventoryStock(ProductID)AS CurrentSupply   FROM Production.Product   WHERE ProductModelID BETWEEN 75 and 80;

Table-Valued Functions

The following example creates an inline table-valued function (TVF) in the AdventureWorks2012 database. 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.

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

The following example invokes the function and specifies customer ID 602.

            SELECT * FROM Sales.ufn_SalesByStore (602);

The following example creates a multi-statement table-valued function (MSTVF) in the AdventureWorks2012 database. The function takes a single input parameter, an EmployeeID and returns a list of all the employees who report to the specified employee directly or indirectly. The function is then invoked specifying employee ID 109.

            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

The following example invokes the function and specifies employee ID 1.

            SELECT EmployeeID, FirstName, LastName, JobTitle, RecursionLevel   FROM dbo.ufn_FindReports(1);

Note

For more information and examples of inline table-valued functions (inline TVFs) and multi-statement table-valued functions (MSTVFs), see CREATE FUNCTION (Transact-SQL).

Best Practices

If a user-defined function (UDF) is not created with the SCHEMABINDING clause, changes that are made to underlying objects can affect the definition of the function and produce unexpected results when it is invoked. We recommend that you implement one of the following methods to ensure that the function does not become outdated because of changes to its underlying objects:

Specify the WITH SCHEMABINDING clause when you are creating the UDF. This ensures that the objects referenced in the function definition cannot be modified unless the function is also modified.
Execute the sp_refreshsqlmodule stored procedure after modifying any object that is specified in the definition of the UDF.

If creating a UDF that does not access data, specify the SCHEMABINDING option. This will prevent the query optimizer from generating unnecessary spool operators for query plans involving these UDFs. For more information on spools, see Showplan Logical and Physical Operators Reference. For more information on creating a schema bound function, see Schema-bound functions.

Joining to an MSTVF in a FROM clause is possible, but can result in poor performance. SQL Server is unable to use all the optimized techniques on some statements that can be included in a MSTVF, resulting in a suboptimal query plan. To obtain the best possible performance, whenever possible use joins between base tables instead of functions.

Important

MSTVFs have a fixed cardinality guess of 100 starting with SQL Server 2014 (12.x), and 1 for earlier SQL Server versions.
Starting with SQL Server 2017 (14.x), optimizing an execution plan that uses MSTVFs can leverage interleaved execution, which results in using actual cardinality instead of the above heuristics.
For more information, see Interleaved execution for multi-statement table valued functions.

Note

ANSI_WARNINGS is not honored when you pass parameters in a stored procedure, user-defined function, or when you declare and set variables in a batch statement. For example, if a variable is defined as char(3), and then set to a value larger than three characters, the data is truncated to the defined size and the INSERT or UPDATE statement succeeds.

Feedback