Execute User-defined Functions
Topic Status: Some information in this topic is preview and subject to change in future releases. Preview information describes new features or changes to existing features in Microsoft SQL Server 2016 Community Technology Preview 2 (CTP2).
You can execute a user defined function in SQL Server 2016 by using Transact-SQL.
In This Topic
Limitations and Restrictions
In Transact-SQL, parameters can be supplied either by using value or by using @parameter_name = value. A parameter is not part of a transaction; therefore, if a parameter is changed in a transaction that is later rolled back, the value of the parameter does not revert to its previous value. The value returned to the caller is always the value at the time the module returns.
Permissions are not required to run the EXECUTE statement. However, permissions are required on the securables that are 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 (Transact-SQL)
To execute a user-defined function
In Object Explorer, connect to an instance of Database Engine.
On the Standard bar, click New Query.
Copy and paste the following example into the query window and click Execute.
USE AdventureWorks2012; GO -- Declares a variable and sets it to zero. -- This variable is used to return the results of the function. DECLARE @ret nvarchar(15)= NULL; -- Executes the dbo.ufnGetSalesOrderStatusText function. --The function requires a value for one parameter, @Status. EXEC @ret = dbo.ufnGetSalesOrderStatusText @Status= 5; --Returns the result in the message tab. PRINT @ret;
For more information, see EXECUTE (Transact-SQL).