EXECUTE (Transact-SQL)EXECUTE (Transact-SQL)

適用於: 是SQL Server 是Azure SQL Database 是Azure SQL 資料倉儲 是平行處理資料倉儲 APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure SQL Data Warehouse yesParallel Data Warehouse

Transact-SQLTransact-SQL 批次或下列其中一個模組中執行命令字串或字元字串:系統預存程序、使用者定義預存程序、CLR 預存程序、純量值使用者定義函數或擴充預存程序。Executes a command string or character string within a Transact-SQLTransact-SQL batch, or one of the following modules: system stored procedure, user-defined stored procedure, CLR stored procedure, scalar-valued user-defined function, or extended stored procedure. EXECUTE 陳述式可用來將傳遞命令傳送到連結伺服器。The EXECUTE statement can be used to send pass-through commands to linked servers. 另外,執行字串或命令所在的內容也可以明確設定。Additionally, the context in which a string or command is executed can be explicitly set. 結果集的中繼資料可透過 WITH RESULT SETS 來定義。Metadata for the result set can be defined by using the WITH RESULT SETS options.

重要

在您利用字元字串呼叫 EXECUTE 之前,請先驗證該字元字串。Before you call EXECUTE with a character string, validate the character string. 千萬不要執行從未經驗證的使用者輸入所建構的命令。Never execute a command constructed from user input that has not been validated.

主題連結圖示 Transact-SQL 語法慣例Topic link icon Transact-SQL Syntax Conventions

語法Syntax

-- Syntax for SQL Server  
  
Execute a stored procedure or function  
[ { EXEC | EXECUTE } ]  
    {   
      [ @return_status = ]  
      { module_name [ ;number ] | @module_name_var }   
        [ [ @parameter = ] { value   
                           | @variable [ OUTPUT ]   
                           | [ DEFAULT ]   
                           }  
        ]  
      [ ,...n ]  
      [ WITH <execute_option> [ ,...n ] ]  
    }  
[;]  
  
Execute a character string  
{ EXEC | EXECUTE }   
    ( { @string_variable | [ N ]'tsql_string' } [ + ...n ] )  
    [ AS { LOGIN | USER } = ' name ' ]  
[;]  
  
Execute a pass-through command against a linked server  
{ EXEC | EXECUTE }  
    ( { @string_variable | [ N ] 'command_string [ ? ]' } [ + ...n ]  
        [ { , { value | @variable [ OUTPUT ] } } [ ...n ] ]  
    )   
    [ AS { LOGIN | USER } = ' name ' ]  
    [ AT linked_server_name ]  
[;]  
  
<execute_option>::=  
{  
        RECOMPILE   
    | { RESULT SETS UNDEFINED }   
    | { RESULT SETS NONE }   
    | { RESULT SETS ( <result_sets_definition> [,...n ] ) }  
}   
  
<result_sets_definition> ::=   
{  
    (  
         { column_name   
           data_type   
         [ COLLATE collation_name ]   
         [ NULL | NOT NULL ] }  
         [,...n ]  
    )  
    | AS OBJECT   
        [ db_name . [ schema_name ] . | schema_name . ]   
        {table_name | view_name | table_valued_function_name }  
    | AS TYPE [ schema_name.]table_type_name  
    | AS FOR XML   
}  
-- In-Memory OLTP   

Execute a natively compiled, scalar user-defined function  
[ { EXEC | EXECUTE } ]   
    {   
      [ @return_status = ]   
      { module_name | @module_name_var }   
        [ [ @parameter = ] { value   
                           | @variable   
                           | [ DEFAULT ]   
                           }  
        ]   
      [ ,...n ]   
      [ WITH <execute_option> [ ,...n ] ]   
    }  
<execute_option>::=  
{  
    | { RESULT SETS UNDEFINED }   
    | { RESULT SETS NONE }   
    | { RESULT SETS ( <result_sets_definition> [,...n ] ) }  
}  
-- Syntax for Azure SQL Database   
  
Execute a stored procedure or function  
[ { EXEC | EXECUTE } ]  
    {   
      [ @return_status = ]  
      { module_name  | @module_name_var }   
        [ [ @parameter = ] { value   
                           | @variable [ OUTPUT ]   
                           | [ DEFAULT ]   
                           }  
        ]  
      [ ,...n ]  
      [ WITH RECOMPILE ]  
    }  
[;]  
  
Execute a character string  
{ EXEC | EXECUTE }   
    ( { @string_variable | [ N ]'tsql_string' } [ + ...n ] )  
    [ AS {  USER } = ' name ' ]  
[;]  
  
<execute_option>::=  
{  
        RECOMPILE   
    | { RESULT SETS UNDEFINED }   
    | { RESULT SETS NONE }   
    | { RESULT SETS ( <result_sets_definition> [,...n ] ) }  
}   
  
<result_sets_definition> ::=   
{  
    (  
         { column_name   
           data_type   
         [ COLLATE collation_name ]   
         [ NULL | NOT NULL ] }  
         [,...n ]  
    )  
    | AS OBJECT   
        [ db_name . [ schema_name ] . | schema_name . ]   
        {table_name | view_name | table_valued_function_name }  
    | AS TYPE [ schema_name.]table_type_name  
    | AS FOR XML  
  
-- Syntax for Azure SQL Data Warehouse and Parallel Data Warehouse  

-- Execute a stored procedure  
[ { EXEC | EXECUTE } ]  
    procedure_name   
        [ { value | @variable [ OUT | OUTPUT ] } ] [ ,...n ] }  
[;]  
  
-- Execute a SQL string  
{ EXEC | EXECUTE }  
    ( { @string_variable | [ N ] 'tsql_string' } [ +...n ] )  
[;]  

引數Arguments

@return_status@return_status
這是儲存模組傳回狀態的選擇性整數變數。Is an optional integer variable that stores the return status of a module. 這個變數必須先在批次、預存程序或函數中宣告之後,才能用在 EXECUTE 陳述式。This variable must be declared in the batch, stored procedure, or function before it is used in an EXECUTE statement.

當您利用 @return_status 變數叫用純量值使用者定義函數時,該變數可以是任何純量資料類型。When used to invoke a scalar-valued user-defined function, the @return_status variable can be of any scalar data type.

module_namemodule_name
這是您要呼叫的預存程序或純量值使用者定義函數的完整或不完整名稱。Is the fully qualified or nonfully qualified name of the stored procedure or scalar-valued user-defined function to call. 模組名稱必須符合識別碼的規則。Module names must comply with the rules for identifiers. 無論伺服器定序為何,擴充預存程序的名稱一定有大小寫區分。The names of extended stored procedures are always case-sensitive, regardless of the collation of the server.

已在另一個資料庫建立的模組,如果執行它的使用者擁有它,或者具有適當的權限可以在該資料庫執行它,就可以執行這個模組。A module that has been created in another database can be executed if the user running the module owns the module or has the appropriate permission to execute it in that database. 如果執行某個模組的使用者,具有適當的權限可以使用該伺服器 (遠端存取),以及在該資料庫中執行該模組,就可以在另一個執行 SQL ServerSQL Server 的伺服器上執行該模組。A module can be executed on another server running SQL ServerSQL Server if the user running the module has the appropriate permission to use that server (remote access) and to execute the module in that database. 如果指定了伺服器名稱,但沒有指定資料庫名稱,則 SQL Server Database EngineSQL Server Database Engine 會在該使用者的預設資料庫中尋找該模組。If a server name is specified but no database name is specified, the SQL Server Database EngineSQL Server Database Engine looks for the module in the default database of the user.

;number;number
適用於SQL Server 2008SQL Server 2008SQL Server 2017SQL Server 2017Applies to: SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017

這是用來將同名程序分組的選擇性整數。Is an optional integer that is used to group procedures of the same name. 這個參數不用於擴充預存程序。This parameter is not used for extended stored procedures.

注意

這項功能處於維護模式,並可能在 Microsoft SQL Server 的未來版本中移除。This feature is in maintenance mode and may be removed in a future version of Microsoft SQL Server. 請避免在新的開發工作中使用這項功能,並規劃修改目前使用這項功能的應用程式。Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

如需有關程序群組的詳細資訊,請參閱 CREATE PROCEDURE (Transact-SQL)For more information about procedure groups, see CREATE PROCEDURE (Transact-SQL).

@module_name_var@module_name_var
這是在本機定義的變數名稱,它代表模組名稱。Is the name of a locally defined variable that represents a module name.

這可以是保存原生編譯純量使用者定義函數名稱的變數。This can be a variable that holds the name of a natively compiled, scalar user-defined function.

@parameter@parameter
這是 module_name 的參數,在模組中定義。Is the parameter for module_name, as defined in the module. 參數名稱前面必須加上 @ 記號 (@)。Parameter names must be preceded by the at sign (@). 搭配 @parameter_name=value 格式使用時,參數名稱和常數不必以它們在模組中定義的順序來提供。When used with the @parameter_name=value form, parameter names and constants do not have to be supplied in the order in which they are defined in the module. 不過,只要有任何參數採用 @parameter_name=value 格式,所有後續的參數就必須採用該格式。However, if the @parameter_name=value form is used for any parameter, it must be used for all subsequent parameters.

依預設,參數可為 Null。By default, parameters are nullable.

valuevalue
這是要傳遞到模組或傳遞命令的參數值。Is the value of the parameter to pass to the module or pass-through command. 如果未指定參數名稱,參數值就必須依照模組所定義的順序來提供。If parameter names are not specified, parameter values must be supplied in the order defined in the module.

在對連結伺服器執行傳遞命令時,參數值的順序會隨著連結伺服器的 OLE DB 提供者而不同。When executing pass-through commands against linked servers, the order of the parameter values depends on the OLE DB provider of the linked server. 大部分的 OLE DB 提供者,都會將值由左到右繫結到參數。Most OLE DB providers bind values to parameters from left to right.

如果參數值是物件名稱、字元字串或者由資料庫名稱或結構描述名稱所限定,則必須以單引號括住整個名稱。If the value of a parameter is an object name, character string, or qualified by a database name or schema name, the whole name must be enclosed in single quotation marks. 如果參數值是關鍵字,則必須以雙引號括住關鍵字。If the value of a parameter is a keyword, the keyword must be enclosed in double quotation marks.

如果您傳遞開頭不是 @ 的單字,且沒有用單引號括住 (例如您忘記參數名稱上的 @),則該單字會被視為 Nvarchar 字串 (儘管遺漏了引號)。If you pass a single word that does not begin with @ and that's not enclosed in quotation marks - for example, if you forget @ on a parameter name - the word is treated as an nvarchar string, in spite of the missing quotation marks.

如果預設值是在模組中定義,使用者就可以直接執行該模組,不必指定參數。If a default is defined in the module, a user can execute the module without specifying a parameter.

預設值也可以是 NULL。The default can also be NULL. 通常,模組定義會指定當參數值為 NULL 時,應該採取什麼動作。Generally, the module definition specifies the action that should be taken if a parameter value is NULL.

@variable@variable
這是儲存參數或傳回參數的變數。Is the variable that stores a parameter or a return parameter.

OUTPUTOUTPUT
指定讓模組或命令字串傳回參數。Specifies that the module or command string returns a parameter. 模組或命令字串中的相符參數也必須先利用 OUTPUT 關鍵字加以建立。The matching parameter in the module or command string must also have been created by using the keyword OUTPUT. 當您把資料指標變數當做參數使用時,請使用這個關鍵字。Use this keyword when you use cursor variables as parameters.

如果 value 被定義為針對連結伺服器執行之模組的 OUTPUT,則 OLE DB 提供者執行之對應 @parameter 所做的變更會在模組執行結束時複製回變數。If value is defined as OUTPUT of a module executed against a linked server, any changes to the corresponding @parameter performed by the OLE DB provider will be copied back to the variable at the end of the execution of module.

如果 OUTPUT 參數正在使用中,且其目的是在呼叫批次或模組的其他陳述式中使用傳回值,則參數值必須當做變數加以傳遞,例如,@parameter = @variableIf OUTPUT parameters are being used and the intent is to use the return values in other statements within the calling batch or module, the value of the parameter must be passed as a variable, such as @parameter = @variable. 您不可以針對未在模組中定義為 OUTPUT 參數的參數指定 OUTPUT 來執行模組。You cannot execute a module by specifying OUTPUT for a parameter that is not defined as an OUTPUT parameter in the module. 您不可以使用 OUTPUT 將常數傳遞到模組;傳回參數需要一個變數名稱。Constants cannot be passed to module by using OUTPUT; the return parameter requires a variable name. 在執行該程序之前,必須先宣告該變數的資料類型,並且指定一個值。The data type of the variable must be declared and a value assigned before executing the procedure.

當 EXECUTE 針對遠端預存程序使用時,或者針對連結伺服器執行傳遞命令時,OUTPUT 參數就不能是任何一種大型物件 (LOB) 資料類型。When EXECUTE is used against a remote stored procedure, or to execute a pass-through command against a linked server, OUTPUT parameters cannot be any one of the large object (LOB) data types.

傳回參數可以是 LOB 資料類型以外的任何資料類型。Return parameters can be of any data type except the LOB data types.

DEFAULTDEFAULT
提供模組所定義的參數預設值。Supplies the default value of the parameter as defined in the module. 如果該模組所預期的參數值並沒有定義的預設值,而且不是遺漏一個參數,就是指定了 DEFAULT 關鍵字,此時就會發生錯誤。When the module expects a value for a parameter that does not have a defined default and either a parameter is missing or the DEFAULT keyword is specified, an error occurs.

@string_variable@string_variable
這是區域變數的名稱。Is the name of a local variable. @string_variable 可以是任何 charvarcharncharnvarchar 資料類型。 @string_variable can be any char, varchar, nchar, or nvarchar data type. 其中包含 (max) 資料類型在內。These include the (max) data types.

[N] 'tsql_string'[N] 'tsql_string'
常數字串。Is a constant string. tsql_string 可以是任何 nvarcharvarchar 資料類型。tsql_string can be any nvarchar or varchar data type. 如果包含 N,則字串解譯為 nvarchar 資料類型。If the N is included, the string is interpreted as nvarchar data type.

AS <context_specification>AS <context_specification>
指定執行陳述式的內容。Specifies the context in which the statement is executed.

登入LOGIN
適用於SQL Server 2008SQL Server 2008SQL Server 2017SQL Server 2017Applies to: SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017

指定您要模擬的內容是登入。Specifies the context to be impersonated is a login. 模擬範圍是伺服器。The scope of impersonation is the server.

使用者USER
指定您要模擬的內容是目前資料庫中的使用者。Specifies the context to be impersonated is a user in the current database. 模擬範圍僅限於目前資料庫。The scope of impersonation is restricted to the current database. 通往資料庫使用者的內容切換不會繼承該使用者的伺服器層級權限。A context switch to a database user does not inherit the server-level permissions of that user.

重要

如果通往資料庫使用者的內容切換在使用中,任何人想要存取資料庫以外的資源,都會導致陳述式失敗。While the context switch to the database user is active, any attempt to access resources outside the database will cause the statement to fail. 其中包括 USE database 陳述式、分散式查詢以及使用三部分或四部分識別碼來參考另一個資料庫的查詢。This includes USE database statements, distributed queries, and queries that reference another database by using three- or four-part identifiers.

'name''name'
有效的使用者或登入名稱。Is a valid user or login name. name 必須是 sysadmin 固定伺服器角色的成員,或是以主體形式分別存在於 sys.database_principalssys.server_principals 中。name must be a member of the sysadmin fixed server role or exist as a principal in sys.database_principals or sys.server_principals, respectively.

name 不可以是內建帳戶,例如 NT AUTHORITY\LocalService、NT AUTHORITY\NetworkService 或 NT AUTHORITY\LocalSystem。name cannot be a built-in account, such as NT AUTHORITY\LocalService, NT AUTHORITY\NetworkService, or NT AUTHORITY\LocalSystem.

如需詳細資訊,請參閱本主題稍後的指定使用者或登入名稱For more information, see Specifying a User or Login Name later in this topic.

[N] 'command_string'[N] 'command_string'
這是包含要傳遞到連結伺服器之命令的常數字串。Is a constant string that contains the command to be passed through to the linked server. 如果包含 N,則字串解譯為 nvarchar 資料類型。If the N is included, the string is interpreted as nvarchar data type.

[?][?]
指出為其在 EXEC('...', <arg-list>) AT <linkedsrv> 陳述式所使用之通過命令的 <arg-list> 中提供值的參數。Indicates parameters for which values are supplied in the <arg-list> of pass-through commands that are used in an EXEC('...', <arg-list>) AT <linkedsrv> statement.

AT linked_server_nameAT linked_server_name
適用對象SQL Server 2008SQL Server 2008SQL Server 2017SQL Server 2017Applies to: SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017

指定 command_stringlinked_server_name 執行,並將結果 (若有) 傳回用戶端。Specifies that command_string is executed against linked_server_name and results, if any, are returned to the client. linked_server_name 必須參考本機伺服器中現有的連結伺服器定義。linked_server_name must refer to an existing linked server definition in the local server. 連結伺服器是利用 sp_addlinkedserver 所定義。Linked servers are defined by using sp_addlinkedserver.

WITH <execute_option>WITH <execute_option>
可能的執行選項。Possible execute options. INSERT...EXEC 陳述式中不可指定 RESULT SETS 選項。The RESULT SETS options cannot be specified in an INSERT...EXEC statement.

詞彙Term 定義Definition
RECOMPILERECOMPILE 在執行模組之後,強制編譯、使用和捨棄新計畫。Forces a new plan to be compiled, used, and discarded after the module is executed. 如果該模組有現有的查詢計畫,這個計畫便會保留在快取中。If there is an existing query plan for the module, this plan remains in the cache.

如果您所提供的參數不合規則,或者如果資料已經大幅變更,請使用這個選項。Use this option if the parameter you are supplying is atypical or if the data has significantly changed. 這個選項不適用於擴充預存程序。This option is not used for extended stored procedures. 我們建議您少用這個選項,因為它的成本很高。We recommend that you use this option sparingly because it is expensive.

注意: 呼叫使用 OPENDATASOURCE 語法的預存程序時,您無法使用 WITH RECOMPILE。Note: You can not use WITH RECOMPILE when calling a stored procedure that uses OPENDATASOURCE syntax. 指定物件名稱四部分時,會忽略 WITH RECOMPILE 選項。The WITH RECOMPILE option is ignored when a four-part object name is specified.

注意: RECOMPILE 不支援搭配原生編譯的純量使用者定義函式。Note: RECOMPILE is not supported with natively compiled, scalar user-defined functions. 如果您需要重新編譯,請使用 sp_recompile (Transact-SQL)If you need to recompile, use sp_recompile (Transact-SQL).
RESULT SETS UNDEFINEDRESULT SETS UNDEFINED 適用對象SQL Server 2012 (11.x)SQL Server 2012 (11.x)SQL Server 2017SQL Server 2017Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL Server 2012 (11.x)SQL Server 2012 (11.x) through SQL Server 2017SQL Server 2017, Azure SQL DatabaseAzure SQL Database.

這個選項不保證會傳回何種結果 (如果有的話),也不提供定義。This option provides no guarantee of what results, if any, will be returned, and no definition is provided. 如果傳回任何結果或未傳回結果,陳述式會正確無誤地執行。The statement executes without error if any results are returned or no results are returned. 如果未提供 result_sets_option,RESULT SETS UNDEFINED 是預設行為。RESULT SETS UNDEFINED is the default behavior if a result_sets_option is not provided.

對於解譯的純量使用者定義函數和原生編譯的純量使用者定義函數,這個選項沒有作用,因為函數永遠不會傳回結果集。For interpreted scalar user-defined functions, and natively compiled scalar user-defined functions, this option is not operational because the functions never return a result set.
RESULT SETS NONERESULT SETS NONE 適用對象SQL Server 2012 (11.x)SQL Server 2012 (11.x)SQL Server 2017SQL Server 2017Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL Server 2012 (11.x)SQL Server 2012 (11.x) through SQL Server 2017SQL Server 2017, Azure SQL DatabaseAzure SQL Database.

保證 Execute 陳述式不會傳回任何結果。Guarantees that the execute statement will not return any results. 如果傳回任何結果,會中止批次。If any results are returned the batch is aborted.

對於解譯的純量使用者定義函數和原生編譯的純量使用者定義函數,這個選項沒有作用,因為函數永遠不會傳回結果集。For interpreted scalar user-defined functions, and natively compiled scalar user-defined functions, this option is not operational because the functions never return a result set.
<result_sets_definition><result_sets_definition> 適用對象SQL Server 2012 (11.x)SQL Server 2012 (11.x)SQL Server 2017SQL Server 2017Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL Server 2012 (11.x)SQL Server 2012 (11.x) through SQL Server 2017SQL Server 2017, Azure SQL DatabaseAzure SQL Database.

保證結果會依 result_sets_definition 中所指定傳回。Provides a guarantee that the result will come back as specified in the result_sets_definition. 針對傳回多個結果集的陳述式,請提供多個 result_sets_definition 區段。For statements that return multiple result sets, provide multiple result_sets_definition sections. 將每個 result_sets_definition 括在括號中,並以逗號分隔。Enclose each result_sets_definition in parentheses, separated by commas. 如需詳細資訊,請參閱本主題稍後的 <result_sets_definition>。For more information, see <result_sets_definition> later in this topic.

此選項對於原生編譯的純量使用者定義函數一律會產生錯誤,因為函數永遠不會傳回結果集。This option always results in an error for natively compiled, scalar user-defined functions because the functions never return a result set.

<result_sets_definition >適用對象SQL Server 2012 (11.x)SQL Server 2012 (11.x)SQL Server 2017SQL Server 2017Azure SQL DatabaseAzure SQL Database<result_sets_definition> Applies to: SQL Server 2012 (11.x)SQL Server 2012 (11.x) through SQL Server 2017SQL Server 2017, Azure SQL DatabaseAzure SQL Database

描述執行的陳述式所傳回的結果集。Describes the result sets returned by the executed statements. result_sets_definition 子句有下列的意義The clauses of the result_sets_definition have the following meaning

詞彙Term 定義Definition
{{

column_namecolumn_name

data_typedata_type

[ COLLATE collation_name][ COLLATE collation_name]

[NULL | NOT NULL][NULL | NOT NULL]

}}
請參閱下表。See the table below.
db_namedb_name 包含資料表、檢視表或資料表值函式的資料庫名稱。The name of the database containing the table, view or table valued function.
schema_nameschema_name 擁有資料表、檢視表或資料表值函式的結構描述名稱。The name of the schema owning the table, view or table valued function.
table_name | view_name | table_valued_function_nametable_name | view_name | table_valued_function_name 指定傳回的資料行將會是具名資料表、檢視表或資料表值函式中所指定的資料行。Specifies that the columns returned will be those specified in the table, view or table valued function named. AS 物件語法不支援資料表變數、暫存資料表和同義字。Table variables, temporary tables, and synonyms are not supported in the AS object syntax.
AS TYPE [schema_name.]table_type_nameAS TYPE [schema_name.]table_type_name 指定傳回的資料行將會是資料表類型中所指定的資料行。Specifies that the columns returned will be those specified in the table type.
AS FOR XMLAS FOR XML 指定 EXECUTE 陳述式所呼叫的陳述式或預存程序產生的 XML 結果將會轉換成如同是由 SELECT ...FOR XML ... 陳述式產生的格式。Specifies that the XML results from the statement or stored procedure called by the EXECUTE statement will be converted into the format as though they were produced by a SELECT ... FOR XML ... statement. 來自原始陳述式中型別指示詞的所有格式都會移除,而傳回的結果會如同未指定任何型別指示詞。All formatting from the type directives in the original statement are removed, and the results returned are as though no type directive was specified. AS FOR XML 不會將非 XML 表格式結果從執行的陳述式或預存程序轉換為 XML。AS FOR XML does not convert non-XML tabular results from the executed statement or stored procedure into XML.
詞彙Term 定義Definition
column_namecolumn_name 每個資料行的名稱。The names of each column. 如果資料行數目不同於結果集,就會發生錯誤並且中止批次。If the number of columns differs from the result set, an error occurs and the batch is aborted. 如果資料行名稱不同於結果集,傳回的資料行名稱會設為已定義的名稱。If the name of a column differs from the result set, the column name returned will be set to the name defined.
data_typedata_type 每個資料行的資料類型。The data types of each column. 如果資料類型不同,則會隱含轉換成已定義的資料類型。If the data types differ, an implicit conversion to the defined data type is performed. 如果轉換失敗,則會中止批次。If the conversion fails the batch is aborted
COLLATE collation_nameCOLLATE collation_name 每個資料行的定序。The collation of each column. 如果定序不符,則會嘗試隱含定序。If there is a collation mismatch, an implicit collation is attempted. 如果定序失敗,則會中止批次。If that fails, the batch is aborted.
NULL | NOT NULLNULL | NOT NULL 每個資料行的 Null 屬性。The nullability of each column. 如果已定義的 Null 屬性是 NOT NULL,而且傳回的資料包含 NULL,則會發生錯誤並且中止批次。If the defined nullability is NOT NULL and the data returned contains NULLs an error occurs and the batch is aborted. 如果未指定,預設值就會符合 ANSI_NULL_DFLT_ON 和 ANSI_NULL_DFLT_OFF 選項的設定值。If not specified, the default value conforms to the setting of the ANSI_NULL_DFLT_ON and ANSI_NULL_DFLT_OFF options.

執行期間傳回的實際結果集可能在下列方面不同於透過 WITH RESULT SETS 選項所定義的結果:結果集數目、資料行數目、資料行名稱、Null 屬性和資料類型。The actual result set being returned during execution can differ from the result defined using the WITH RESULT SETS clause in one of the following ways: number of result sets, number of columns, column name, nullability, and data type. 如果結果集數目不同,就會發生錯誤並且中止批次。If the number of result sets differs, an error occurs and the batch is aborted.

RemarksRemarks

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

當一個模組呼叫另一個模組,或者參考 Common Language Runtime (CLR) 模組、使用者定義類型或彙總來執行 Managed 程式碼時,就會產生巢狀結構。Nesting occurs when one module calls another or executes managed code by referencing a common language runtime (CLR) module, user-defined type, or aggregate. 當被呼叫的模組或 Managed 程式碼參考開始執行時,巢狀層級便開始累加;而當被呼叫的模組或 Managed 程式碼參考完成時,就開始遞減。The nesting level is incremented when the called module or managed code reference starts execution, and it is decremented when the called module or managed code reference has finished. 如果超過 32 個巢狀層級上限,完整的呼叫鏈便會失敗。Exceeding the maximum of 32 nesting levels causes the complete calling chain to fail. 目前巢狀層級是儲存在 @@NESTLEVEL 系統函數中。The current nesting level is stored in the @@NESTLEVEL system function.

由於遠端預存程序和擴充預存程序不在交易範圍內 (除非在 BEGIN DISTRIBUTED TRANSACTION 陳述式中發出,或者搭配各種組態選項使用),因此透過呼叫它們來執行的命令將無法回復。Because remote stored procedures and extended stored procedures are not within the scope of a transaction (unless issued within a BEGIN DISTRIBUTED TRANSACTION statement or when used with various configuration options), commands executed through calls to them cannot be rolled back. 如需詳細資訊,請參閱系統預存程序 (Transact-SQL)BEGIN DISTRIBUTED TRANSACTION (Transact-SQL)For more information, see System Stored Procedures (Transact-SQL) and BEGIN DISTRIBUTED TRANSACTION (Transact-SQL).

當您使用資料指標變數時,如果您執行的程序所傳遞的資料指標變數配置了一個資料指標,此時就會發生錯誤。When you use cursor variables, if you execute a procedure that passes in a cursor variable with a cursor allocated to it an error occurs.

在執行模組時,如果陳述式是批次中的第一個陳述式,則不必指定 EXECUTE 關鍵字。You do not have to specify the EXECUTE keyword when executing modules if the statement is the first one in a batch.

如需 CLR 預存程序特定的詳細資訊,請參閱<CLR 預存程序>。For additional information specific to CLR stored procedures, see CLR Stored Procedures.

搭配預存程序使用 EXECUTEUsing EXECUTE with Stored Procedures

當您執行預存程序時,如果陳述式是批次中的第一個陳述式,則不必指定 EXECUTE 關鍵字。You do not have to specify the EXECUTE keyword when you execute stored procedures when the statement is the first one in a batch.

SQL ServerSQL Server 系統預存程序是以 sp_ 字元開頭。system stored procedures start with the characters sp_. 它們實際上是儲存在 Resource 資料庫中,但在邏輯上是顯示在每個系統和使用者定義資料庫的 sys 結構描述中。They are physically stored in the Resource database, but logically appear in the sys schema of every system and user-defined database. 當您在批次或模組內 (例如,使用者定義的預存程序或函數) 執行系統預存程序時,我們建議您以 sys 結構描述名稱來限定預存程序名稱。When you execute a system stored procedure, either in a batch or inside a module such as a user-defined stored procedure or function, we recommend that you qualify the stored procedure name with the sys schema name.

SQL ServerSQL Server 系統擴充預存程序是以 xp_ 字元開頭,而且這些字元包含在 master 資料庫的 dbo 結構描述中。system extended stored procedures start with the characters xp_, and these are contained in the dbo schema of the master database. 當您在批次或模組內 (例如,使用者定義的預存程序或函數) 執行系統擴充預存程序時,我們建議您以 master.dbo 來限定預存程序的名稱。When you execute a system extended stored procedure, either in a batch or inside a module such as a user-defined stored procedure or function, we recommend that you qualify the stored procedure name with master.dbo.

當您在批次或模組內 (例如,使用者定義的預存程序或函數) 執行使用者定義的預存程序時,我們建議您以結構描述名稱來限定預存程序名稱。When you execute a user-defined stored procedure, either in a batch or inside a module such as a user-defined stored procedure or function, we recommend that you qualify the stored procedure name with a schema name. 我們不建議您使用與系統預存程序相同的名稱,為使用者定義預存程序命名。We do not recommend that you name a user-defined stored procedure with the same name as a system stored procedure. 如需執行預存程序的詳細資訊,請參閱執行預存程序For more information about executing stored procedures, see Execute a Stored Procedure.

搭配字元字串使用 EXECUTEUsing EXECUTE with a Character String

在舊版的 SQL ServerSQL Server 中,字元字串不能超過 8,000 位元組。In earlier versions of SQL ServerSQL Server, character strings are limited to 8,000 bytes. 這需要串連大型字串來進行動態執行工作。This requires concatenating large strings for dynamic execution. SQL ServerSQL Server 中,您可以指定 varchar(max)nvarchar(max) 資料類型,讓它接受最多達 2 GB 資料的字元字串。In SQL ServerSQL Server, the varchar(max) and nvarchar(max) data types can be specified that allow for character strings to be up to 2 gigabytes of data.

您對資料庫內容所做的變更,只會維持到 EXECUTE 陳述式結束為止。Changes in database context last only until the end of the EXECUTE statement. 例如,當下面這個陳述式中的 EXEC 執行之後,資料庫內容為 master。For example, after the EXEC in this following statement is run, the database context is master.

USE master; EXEC ('USE AdventureWorks2012; SELECT BusinessEntityID, JobTitle FROM HumanResources.Employee;');  

內容切換Context Switching

您可以使用 AS { LOGIN | USER } = ' name ' 子句來切換動態陳述式的執行內容。You can use the AS { LOGIN | USER } = ' name ' clause to switch the execution context of a dynamic statement. 當內容切換被指定為 EXECUTE ('string') AS <context_specification> 時,內容切換的持續時間就限於目前所執行的查詢範圍內。When the context switch is specified as EXECUTE ('string') AS <context_specification>, the duration of the context switch is limited to the scope of the query being executed.

指定使用者或登入名稱Specifying a User or Login Name

AS { LOGIN | USER } = ' name ' 中所指定的使用者或登入名稱,必須以主體形式分別存在於 sys.database_principals 或 sys.server_principal 中,否則陳述式就會失敗。The user or login name specified in AS { LOGIN | USER } = ' name ' must exist as a principal in sys.database_principals or sys.server_principals, respectively, or the statement will fail. 此外,還必須授與主體的 IMPERSONATE 權限。Additionally, IMPERSONATE permissions must be granted on the principal. 除非呼叫端是資料庫擁有者或是系統管理員 (sysadmin) 固定伺服器角色的成員,否則主體必須存在,即使當使用者透過 Windows 群組成員資格存取資料庫或 SQL ServerSQL Server 的執行個體時也一樣。Unless the caller is the database owner or is a member of the sysadmin fixed server role, the principal must exist even when the user is accessing the database or instance of SQL ServerSQL Server through a Windows group membership. 例如,假設有下列情況:For example, assume the following conditions:

  • CompanyDomain\SQLUsers 群組擁有 Sales 資料庫的存取權。CompanyDomain\SQLUsers group has access to the Sales database.

  • CompanyDomain\SqlUser1 是 SQLUsers 的成員,因此對 Sales 資料庫具有隱含的存取權。CompanyDomain\SqlUser1 is a member of SQLUsers and, therefore, has implicit access to the Sales database.

雖然 CompanyDomain\SqlUser1 擁有透過 SQLUsers 群組中的成員資格,對資料庫進行存取的權限,但是 EXECUTE @string_variable AS USER = 'CompanyDomain\SqlUser1' 陳述式會失敗,因為 CompanyDomain\SqlUser1 並未以主體形式存在於資料庫中。Although CompanyDomain\SqlUser1 has access to the database through membership in the SQLUsers group, the statement EXECUTE @string_variable AS USER = 'CompanyDomain\SqlUser1' will fail because CompanyDomain\SqlUser1 does not exist as a principal in the database.

最佳作法Best Practices

指定一個登入或使用者,它具有執行在陳述式或模組中定義的作業時所需要的最低權限。Specify a login or user that has the least privileges required to perform the operations that are defined in the statement or module. 例如,如果只需要資料庫層級權限,就不要指定具有伺服器層級權限的登入名稱;或者除非需要其權限,否則不要指定資料庫擁有者帳戶。For example, do not specify a login name, which has server-level permissions, if only database-level permissions are required; or do not specify a database owner account unless those permissions are required.

權限Permissions

執行 EXECUTE 陳述式不需要任何權限。Permissions are not required to run the EXECUTE statement. 不過,您必須對 EXECUTE 字串內所參考的安全性實體具備權限。However, permissions are required on the securables that are 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 權限預設會授與模組的擁有者,這位擁有者可以將這些權限轉讓給其他使用者。EXECUTE permissions for a module default to the owner of the module, who can transfer them to other users. 當您執行某個模組來執行字串時,將會檢查執行此模組之使用者內容中的權限,不過不會檢查建立模組之使用者內容中的權限。When a module is run that executes a string, permissions are checked in the context of the user who executes the module, not in the context of the user who created the module. 然而,如果同一位使用者擁有呼叫模組,而該模組正被呼叫時,第二個模組就不會再檢查一次 EXECUTE 權限。However, if the same user owns the calling module and the module being called, EXECUTE permission checking is not performed for the second module.

如果模組要存取其他資料庫物件,只要您具有該模組的 EXECUTE 權限,而且下列一項為真,就可以順利執行:If the module accesses other database objects, execution succeeds when you have EXECUTE permission on the module and one of the following is true:

  • 模組被標示為 EXECUTE AS USER 或 SELF,而且模組擁有者具有參考物件的對應權限。The module is marked EXECUTE AS USER or SELF, and the module owner has the corresponding permissions on the referenced object. 如需有關模組中模擬的詳細資訊,請參閱 EXECUTE AS 子句 (Transact-SQL)For more information about impersonation within a module, see EXECUTE AS Clause (Transact-SQL).

  • 模組被標示為 EXECUTE AS CALLER,而且您具有物件的對應權限。The module is marked EXECUTE AS CALLER, and you have the corresponding permissions on the object.

  • 模組被標示為 EXECUTE AS user_name,而且 user_name 具有物件的對應權限。The module is marked EXECUTE AS user_name, and user_name has the corresponding permissions on the object.

內容切換權限Context Switching Permissions

若要指定某項登入的 EXECUTE AS 權限,則這位呼叫端必須具有指定之登入名稱的 IMPERSONATE 權限。To specify EXECUTE AS on a login, the caller must have IMPERSONATE permissions on the specified login name. 若要指定資料庫使用者的 EXECUTE AS 權限,則這位呼叫端必須具有指定之使用者名稱的 IMPERSONATE 權限。To specify EXECUTE AS on a database user, the caller must have IMPERSONATE permissions on the specified user name. 如果未指定任何執行內容,或者沒有指定 EXECUTE AS CALLER,就不需要 IMPERSONATE 權限。When no execution context is specified, or EXECUTE AS CALLER is specified, IMPERSONATE permissions are not required.

範例Examples

A.A. 使用 EXECUTE 傳遞單一參數Using EXECUTE to pass a single parameter

AdventureWorks2012AdventureWorks2012 資料庫中的 uspGetEmployeeManagers 預存程序預期需要一個參數 (@EmployeeID)。The uspGetEmployeeManagers stored procedure in the AdventureWorks2012AdventureWorks2012 database expects one parameter (@EmployeeID). 下列範例會將 Employee ID 6 當做參數值來執行 uspGetEmployeeManagers 預存程序。The following examples execute the uspGetEmployeeManagers stored procedure with Employee ID 6 as its parameter value.

EXEC dbo.uspGetEmployeeManagers 6;  
GO  

此變數可以在執行作業中明確命名:The variable can be explicitly named in the execution:

EXEC dbo.uspGetEmployeeManagers @EmployeeID = 6;  
GO  

如果下列陳述式是批次、osqlsqlcmd 指令碼中的第一個陳述式,即不需要 EXEC。If the following is the first statement in a batch or an osql or sqlcmd script, EXEC is not required.

dbo.uspGetEmployeeManagers 6;  
GO  
--Or  
dbo.uspGetEmployeeManagers @EmployeeID = 6;  
GO  

B.B. 使用多個參數Using multiple parameters

下列範例會執行 spGetWhereUsedProductID 資料庫中的 AdventureWorks2012AdventureWorks2012 預存程序。The following example executes the spGetWhereUsedProductID stored procedure in the AdventureWorks2012AdventureWorks2012 database. 它會傳遞兩個參數:第一個參數是產品識別碼 (819),第二個參數 @CheckDate, 則是 datetime 值。It passes two parameters: the first parameter is a product ID (819) and the second parameter, @CheckDate, is a datetime value.

DECLARE @CheckDate datetime;  
SET @CheckDate = GETDATE();  
EXEC dbo.uspGetWhereUsedProductID 819, @CheckDate;  
GO  

C.C. 使用 EXECUTE 'tsql_string' 與變數Using EXECUTE 'tsql_string' with a variable

下列範例會示範 EXECUTE 如何處理含有變數的動態建立字串。The following example shows how EXECUTE handles dynamically built strings that contain variables. 這個範例會建立 tables_cursor 資料指標來保存一份 AdventureWorks2012AdventureWorks2012 資料庫中所有使用者定義資料表的清單,然後再利用這份清單在資料表上重建所有的索引。This example creates the tables_cursor cursor to hold a list of all user-defined tables in the AdventureWorks2012AdventureWorks2012 database, and then uses that list to rebuild all indexes on the tables.

DECLARE tables_cursor CURSOR  
   FOR  
   SELECT s.name, t.name   
   FROM sys.objects AS t  
   JOIN sys.schemas AS s ON s.schema_id = t.schema_id  
   WHERE t.type = 'U';  
OPEN tables_cursor;  
DECLARE @schemaname sysname;  
DECLARE @tablename sysname;  
FETCH NEXT FROM tables_cursor INTO @schemaname, @tablename;  
WHILE (@@FETCH_STATUS <> -1)  
BEGIN;  
   EXECUTE ('ALTER INDEX ALL ON ' + @schemaname + '.' + @tablename + ' REBUILD;');  
   FETCH NEXT FROM tables_cursor INTO @schemaname, @tablename;  
END;  
PRINT 'The indexes on all tables have been rebuilt.';  
CLOSE tables_cursor;  
DEALLOCATE tables_cursor;  
GO  
  

D.D. 使用 EXECUTE 與遠端預存程序Using EXECUTE with a remote stored procedure

下列範例會在遠端伺服器 uspGetEmployeeManagers 中執行 SQLSERVER1 預存程序,並且儲存傳回狀態,指出 @retstat 是成功還是失敗。The following example executes the uspGetEmployeeManagers stored procedure on the remote server SQLSERVER1 and stores the return status that indicates success or failure in @retstat.

適用於SQL Server 2008SQL Server 2008SQL Server 2017SQL Server 2017Applies to: SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017

DECLARE @retstat int;  
EXECUTE @retstat = SQLSERVER1.AdventureWorks2012.dbo.uspGetEmployeeManagers @BusinessEntityID = 6;  

E.E. 使用 EXECUTE 與預存程序變數Using EXECUTE with a stored procedure variable

下列範例會建立一個代表預存程序名稱的變數。The following example creates a variable that represents a stored procedure name.

DECLARE @proc_name varchar(30);  
SET @proc_name = 'sys.sp_who';  
EXEC @proc_name;  
  

F.F. 使用 EXECUTE 與 DEFAULTUsing EXECUTE with DEFAULT

下列範例會針對第一個和第三個參數採用預設值來建立預存程序。The following example creates a stored procedure with default values for the first and third parameters. 在執行程序時,如果呼叫中沒有傳遞任何值,或者如果未指定預設值,就會針對第一個和第三個參數插入這些預設值。When the procedure is run, these defaults are inserted for the first and third parameters when no value is passed in the call or when the default is specified. 請注意各種可以使用 DEFAULT 關鍵字的方法。Note the various ways the DEFAULT keyword can be used.

IF OBJECT_ID(N'dbo.ProcTestDefaults', N'P')IS NOT NULL  
   DROP PROCEDURE dbo.ProcTestDefaults;  
GO  
-- Create the stored procedure.  
CREATE PROCEDURE dbo.ProcTestDefaults (  
@p1 smallint = 42,   
@p2 char(1),   
@p3 varchar(8) = 'CAR')  
AS   
   SET NOCOUNT ON;  
   SELECT @p1, @p2, @p3  
;  
GO  
  

您可以利用多種組合執行 Proc_Test_Defaults 預存程序。The Proc_Test_Defaults stored procedure can be executed in many combinations.

-- Specifying a value only for one parameter (@p2).  
EXECUTE dbo.ProcTestDefaults @p2 = 'A';  
-- Specifying a value for the first two parameters.  
EXECUTE dbo.ProcTestDefaults 68, 'B';  
-- Specifying a value for all three parameters.  
EXECUTE dbo.ProcTestDefaults 68, 'C', 'House';  
-- Using the DEFAULT keyword for the first parameter.  
EXECUTE dbo.ProcTestDefaults @p1 = DEFAULT, @p2 = 'D';  
-- Specifying the parameters in an order different from the order defined in the procedure.  
EXECUTE dbo.ProcTestDefaults DEFAULT, @p3 = 'Local', @p2 = 'E';  
-- Using the DEFAULT keyword for the first and third parameters.  
EXECUTE dbo.ProcTestDefaults DEFAULT, 'H', DEFAULT;  
EXECUTE dbo.ProcTestDefaults DEFAULT, 'I', @p3 = DEFAULT;  
  

G.G. 使用 EXECUTE 與 AT linked_server_nameUsing EXECUTE with AT linked_server_name

下列範例會傳遞一個命令字串到遠端伺服器。The following example passes a command string to a remote server. 它會建立一個連結伺服器 SeattleSales,指向 SQL ServerSQL Server 的另一個執行個體,然後針對該連結伺服器執行 DDL 陳述式 (CREATE TABLE)。It creates a linked server SeattleSales that points to another instance of SQL ServerSQL Server and executes a DDL statement (CREATE TABLE) against that linked server.

適用於SQL Server 2008SQL Server 2008SQL Server 2017SQL Server 2017Applies to: SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017

EXEC sp_addlinkedserver 'SeattleSales', 'SQL Server'  
GO  
EXECUTE ( 'CREATE TABLE AdventureWorks2012.dbo.SalesTbl   
(SalesID int, SalesName varchar(10)) ; ' ) AT SeattleSales;  
GO  

H.H. 使用 EXECUTE WITH RECOMPILEUsing EXECUTE WITH RECOMPILE

下列範例會執行 Proc_Test_Defaults 預存程序,並在執行模組之後,強制編譯、使用和捨棄新的查詢計劃。The following example executes the Proc_Test_Defaults stored procedure and forces a new query plan to be compiled, used, and discarded after the module is executed.

EXECUTE dbo.Proc_Test_Defaults @p2 = 'A' WITH RECOMPILE;  
GO  

I.I. 使用 EXECUTE 與使用者定義函數Using EXECUTE with a user-defined function

下列範例會執行 AdventureWorks2012AdventureWorks2012 資料庫中的 ufnGetSalesOrderStatusText 純量使用者定義函數。The following example executes the ufnGetSalesOrderStatusText scalar user-defined function in the AdventureWorks2012AdventureWorks2012 database. 它會使用變數 @returnstatus 來儲存該函數所傳回的值。It uses the variable @returnstatus to store the value returned by the function. 該函數會預期接受一個輸入參數 @StatusThe function expects one input parameter, @Status. 它定義為 tinyint 資料類型。This is defined as a tinyint data type.

DECLARE @returnstatus nvarchar(15);  
SET @returnstatus = NULL;  
EXEC @returnstatus = dbo.ufnGetSalesOrderStatusText @Status = 2;  
PRINT @returnstatus;  
GO  

J.J. 使用 EXECUTE 查詢連結伺服器上的 Oracle 資料庫Using EXECUTE to query an Oracle database on a linked server

下列範例會執行遠端 Oracle 伺服器上的幾個 SELECT 陳述式。The following example executes several SELECT statements at the remote Oracle server. 這個範例一開始就加入 Oracle 伺服器當做連結伺服器,並且建立連結伺服器登入。The example begins by adding the Oracle server as a linked server and creating linked server login.

適用於SQL Server 2008SQL Server 2008SQL Server 2017SQL Server 2017Applies to: SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017

-- Setup the linked server.  
EXEC sp_addlinkedserver    
        @server='ORACLE',  
        @srvproduct='Oracle',  
        @provider='OraOLEDB.Oracle',   
        @datasrc='ORACLE10';  
  
EXEC sp_addlinkedsrvlogin   
    @rmtsrvname='ORACLE',  
    @useself='false',   
    @locallogin=null,   
    @rmtuser='scott',   
    @rmtpassword='tiger';  
  
EXEC sp_serveroption 'ORACLE', 'rpc out', true;  
GO  
  
-- Execute several statements on the linked Oracle server.  
EXEC ( 'SELECT * FROM scott.emp') AT ORACLE;  
GO  
EXEC ( 'SELECT * FROM scott.emp WHERE MGR = ?', 7902) AT ORACLE;  
GO  
DECLARE @v INT;   
SET @v = 7902;  
EXEC ( 'SELECT * FROM scott.emp WHERE MGR = ?', @v) AT ORACLE;  
GO   

K.K. 使用 EXECUTE AS USER 將內容切換到其他使用者Using EXECUTE AS USER to switch context to another user

下列範例會執行 Transact-SQLTransact-SQL 字串來建立一個資料表,以及指定 AS USER 子句,將陳述式的執行內容從呼叫端切換到 User1The following example executes a Transact-SQLTransact-SQL string that creates a table and specifies the AS USER clause to switch the execution context of the statement from the caller to User1. 執行陳述式時,Database EngineDatabase Engine 會檢查 User1 的權限。The Database EngineDatabase Engine will check the permissions of User1 when the statement is run. User1 必須是資料庫中的使用者,並須具備在 Sales 結構描述中建立資料表的權限,否則陳述式將會失敗。User1 must exist as a user in the database and must have permission to create tables in the Sales schema, or the statement fails.

EXECUTE ('CREATE TABLE Sales.SalesTable (SalesID int, SalesName varchar(10));')  
AS USER = 'User1';  
GO  

L.L. 使用 EXECUTE 與 AT linked_server_nameUsing a parameter with EXECUTE and AT linked_server_name

下列範例會使用問號 (?) 預留位置代表參數,將命令字串傳遞至遠端伺服器。The following example passes a command string to a remote server by using a question mark (?) placeholder for a parameter. 它會建立一個連結伺服器 SeattleSales,指向 SQL ServerSQL Server 的另一個執行個體,然後對該連結伺服器執行 SELECT 陳述式。The example creates a linked server SeattleSales that points to another instance of SQL ServerSQL Server and executes a SELECT statement against that linked server. SELECT 陳述式會使用問號當做 ProductID 參數 (952) 的預留位置,而這會在陳述式之後提供。The SELECT statement uses the question mark as a place holder for the ProductID parameter (952), which is provided after the statement.

適用於SQL Server 2008SQL Server 2008SQL Server 2017SQL Server 2017Applies to: SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017

-- Setup the linked server.  
EXEC sp_addlinkedserver 'SeattleSales', 'SQL Server'  
GO  
-- Execute the SELECT statement.  
EXECUTE ('SELECT ProductID, Name   
    FROM AdventureWorks2012.Production.Product  
    WHERE ProductID = ? ', 952) AT SeattleSales;  
GO  

M.M. 使用 EXECUTE 重新定義單一結果集Using EXECUTE to redefine a single result set

先前某些範例執行 EXEC dbo.uspGetEmployeeManagers 6;,傳回 7 個資料行。Some of the previous examples executed EXEC dbo.uspGetEmployeeManagers 6; which returned 7 columns. 下列範例示範如何使用 WITH RESULT SET 語法來變更傳回結果集的名稱和資料類型。The following example demonstrates using the WITH RESULT SET syntax to change the names and data types of the returning result set.

適用對象SQL Server 2012 (11.x)SQL Server 2012 (11.x)SQL Server 2017SQL Server 2017Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL Server 2012 (11.x)SQL Server 2012 (11.x) through SQL Server 2017SQL Server 2017, Azure SQL DatabaseAzure SQL Database

EXEC uspGetEmployeeManagers 16  
WITH RESULT SETS  
(   
   ([Reporting Level] int NOT NULL,  
    [ID of Employee] int NOT NULL,  
    [Employee First Name] nvarchar(50) NOT NULL,  
    [Employee Last Name] nvarchar(50) NOT NULL,  
    [Employee ID of Manager] nvarchar(max) NOT NULL,  
    [Manager First Name] nvarchar(50) NOT NULL,  
    [Manager Last Name] nvarchar(50) NOT NULL )  
);  
  

N.N. 使用 EXECUTE 重新定義兩個結果集Using EXECUTE to redefine a two result sets

當執行一個會傳回多個結果集的陳述式時,請定義每個預期的結果集。When executing a statement that returns more than one result set, define each expected result set. AdventureWorks2012AdventureWorks2012 中的下列範例會建立一個可傳回兩個結果集的程序。The following example in AdventureWorks2012AdventureWorks2012 creates a procedure that returns two result sets. 然後使用 WITH RESULT SETS 子句,並指定兩個結果集定義,執行此程序。Then the procedure is executed using the WITH RESULT SETS clause, and specifying two result set definitions.

適用對象SQL Server 2012 (11.x)SQL Server 2012 (11.x)SQL Server 2017SQL Server 2017Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL Server 2012 (11.x)SQL Server 2012 (11.x) through SQL Server 2017SQL Server 2017, Azure SQL DatabaseAzure SQL Database

--Create the procedure  
CREATE PROC Production.ProductList @ProdName nvarchar(50)  
AS  
-- First result set  
SELECT ProductID, Name, ListPrice  
    FROM Production.Product  
    WHERE Name LIKE @ProdName;  
-- Second result set   
SELECT Name, COUNT(S.ProductID) AS NumberOfOrders  
    FROM Production.Product AS P  
    JOIN Sales.SalesOrderDetail AS S  
        ON P.ProductID  = S.ProductID   
    WHERE Name LIKE @ProdName  
    GROUP BY Name;  
GO  
  
-- Execute the procedure   
EXEC Production.ProductList '%tire%'  
WITH RESULT SETS   
(  
    (ProductID int,   -- first result set definition starts here  
    Name Name,  
    ListPrice money)  
    ,                 -- comma separates result set definitions  
    (Name Name,       -- second result set definition starts here  
    NumberOfOrders int)  
);  
  

範例:Azure SQL 資料倉儲Azure SQL Data Warehouse平行處理資料倉儲Parallel Data WarehouseExamples: Azure SQL 資料倉儲Azure SQL Data Warehouse and 平行處理資料倉儲Parallel Data Warehouse

範例 O:基本程序執行Example O: Basic Procedure Execution

執行預存程序:Executing a stored procedure:

EXEC proc1;  

在執行階段以決定的名稱呼叫預存程序:Calling a stored procedure with name determined at runtime:

EXEC ('EXEC ' + @var);  

從預存程序內呼叫預存程序:Calling a stored procedure from within a stored procedure:

CREATE sp_first AS EXEC sp_second; EXEC sp_third;  

範例 P:執行字串Example P: Executing Strings

執行 SQL 字串:Executing a SQL string:

EXEC ('SELECT * FROM sys.types');  

執行巢狀字串:Executing a nested string:

EXEC ('EXEC (''SELECT * FROM sys.types'')');  

執行字串變數:Executing a string variable:

DECLARE @stringVar nvarchar(100);  
SET @stringVar = N'SELECT name FROM' + ' sys.sql_logins';  
EXEC (@stringVar);  

範例 Q:具參數的程序Example Q: Procedures with Parameters

下列範例建立有參數的程序,並示範 3 種執行程序的方式:The following example creates a procedure with parameters and demonstrates 3 ways to execute the procedure:

-- Uses AdventureWorks  
  
CREATE PROC ProcWithParameters  
    @name nvarchar(50),  
@color nvarchar (15)  
AS   
SELECT ProductKey, EnglishProductName, Color FROM [dbo].[DimProduct]  
WHERE EnglishProductName LIKE @name  
AND Color = @color;  
GO  
  
-- Executing using positional parameters  
EXEC ProcWithParameters N'%arm%', N'Black';  
-- Executing using named parameters in order  
EXEC ProcWithParameters @name = N'%arm%', @color = N'Black';  
-- Executing using named parameters out of order  
EXEC ProcWithParameters @color = N'Black', @name = N'%arm%';  
GO  

另請參閱See Also

@@NESTLEVEL (Transact-SQL) @@NESTLEVEL (Transact-SQL)
DECLARE @local_variable (Transact-SQL) DECLARE @local_variable (Transact-SQL)
EXECUTE AS 子句 (Transact-SQL) EXECUTE AS Clause (Transact-SQL)
osql 公用程式 osql Utility
主體 (Database Engine) Principals (Database Engine)
REVERT (Transact-SQL) REVERT (Transact-SQL)
sp_addlinkedserver (Transact-SQL) sp_addlinkedserver (Transact-SQL)
sqlcmd 公用程式 sqlcmd Utility
SUSER_NAME (Transact-SQL) SUSER_NAME (Transact-SQL)
sys.database_principals (Transact-SQL) sys.database_principals (Transact-SQL)
sys.server_principals (Transact-SQL) sys.server_principals (Transact-SQL)
USER_NAME (Transact-SQL) USER_NAME (Transact-SQL)
OPENDATASOURCE (Transact-SQL) OPENDATASOURCE (Transact-SQL)
記憶體內部 OLTP 的純量使用者定義函數Scalar User-Defined Functions for In-Memory OLTP