| title | Execute user-defined functions | ||
|---|---|---|---|
| description | Learn how to execute a user defined function using Transact-SQL. | ||
| author | rwestMSFT | ||
| ms.author | randolphwest | ||
| ms.date | 04/26/2024 | ||
| ms.service | sql | ||
| ms.topic | language-reference | ||
| ms.custom |
|
||
| helpviewer_keywords |
|
||
| monikerRange | =azuresqldb-current || >=sql-server-2016 || >=sql-server-linux-2017 || =azuresqldb-mi-current || =fabric-sqldb |
[!INCLUDE SQL Server SQL Database-fabricsqldb]
Execute a user defined function using Transact-SQL.
Scalar functions must be invoked by using at least the two-part name of the function (<schema>.<function>). For more information, see CREATE FUNCTION (Transact-SQL).
In Transact-SQL, parameters can be supplied either by using <value> or by using @parameter_name = <value>. A parameter isn't part of a transaction. Therefore, if a parameter is changed in a transaction that is later rolled back, the value of the parameter doesn't revert to its previous value. The value returned to the caller is always the value at the time the module returns.
Permissions aren't required to run the EXECUTE statement. However, permissions are required on the securables referenced within the EXECUTE string. For example, if the string contains an INSERT statement, the caller of the EXECUTE statement must have INSERT permission on the target table. Permissions are checked at the time EXECUTE statement is encountered, even if the EXECUTE statement is included within a module. For more information, see EXECUTE.
This example uses the ufnGetSalesOrderStatusText scalar-valued function that is available in most editions of [!INCLUDE sssampledbobject-md]. The purpose of the function is to return a text value for sales status from a given integer. Vary the example by passing integers 1 through 7 to the @Status parameter.
USE [AdventureWorks2022]
GO
-- Declare a variable to return the results of the function.
DECLARE @ret NVARCHAR(15);
-- Execute the function while passing a value to the @status parameter
EXEC @ret = dbo.ufnGetSalesOrderStatusText @Status = 5;
-- View the returned value.
-- The Execute and Select statements must be executed at the same time.
SELECT N'Order Status: ' + @ret;Here's the result.
Order Status: Shipped