Execute User-defined Functions

You can execute a user defined function in SQL Server 2012 by using Transact-SQL.

In This Topic

• Before you begin:

  Limitations and Restrictions

  Security

• To execute a user-defined function, using:

  Transact-SQL

Before You Begin

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.

Security

Permissions

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)

[Top]

Using Transact-SQL

To execute a user-defined function

1. In Object Explorer, connect to an instance of Database Engine.

2. On the Standard bar, click New Query.

3. 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).

[Top]