執行使用者定義函數Execute User-defined Functions

適用於: 是SQL Server 否Azure SQL Database 否Azure Synapse Analytics (SQL DW) 否平行處理資料倉儲 APPLIES TO: yesSQL Server yesAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

使用 Transact-SQL 執行使用者定義函數。Execute a user defined function using Transact-SQL.

注意: 前往使用者定義函式建立函式 (Transact SQL 以取得使用者定義函式的詳細資訊。Note: Visit user defined function and Create Function (Transact SQL for more information about user defined functions.

開始之前Before you begin

限制事項Limitations and restrictions

在 Transact-SQL 中,您可以使用 value 或 @parameter_name=value來提供參數。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

執行 EXECUTE 陳述式不需要任何權限。Permissions are not required to run the EXECUTE statement. 不過,您 必須 對 EXECUTE 字串內所參考的安全性實體具備權限。However, permissions are required on the securables referenced within the EXECUTE string. 例如,如果字串包含 INSERT 陳述式,EXECUTE 陳述式的呼叫端就必須有目標資料表的 INSERT 權限。For example, if the string contains an INSERT statement, the caller of the EXECUTE statement must have INSERT permission on the target table. 遇到 EXECUTE 陳述式時會檢查權限,即使模組內包含 EXECUTE 陳述式也一樣。Permissions are checked at the time EXECUTE statement is encountered, even if the EXECUTE statement is included within a module. 如需詳細資訊,請參閱 EXECUTE (Transact-SQL)For more information, see EXECUTE (Transact-SQL)

使用 Transact-SQLUsing Transact-SQL

範例Example

這個範例會使用大部分 ufnGetSalesOrderStatusText 版本中都有提供的 AdventureWorks純量值函數。This example uses the ufnGetSalesOrderStatusText scalar-valued function that is available in most editions of AdventureWorks. 此函數的目的是為了從指定整數傳回銷售狀態的文字值。The purpose of the function is to return a text value for sales status from a given integer. 請將整數 1 到 7 傳遞給 @Status 參數來改變範例。Vary the example by passing integers 1 through 7 to the @Status parameter.

USE [AdventureWorks2016CTP3]
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; 

-- Result:
-- Order Status: Shipped