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

适用于: 是SQL Server(从 2008 开始)是Azure SQL 数据库是Azure SQL 数据仓库是并行数据仓库APPLIES TO: yesSQL Server (starting with 2008) 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 数据库引擎SQL Server Database Engine会在用户的默认数据库中查找该模块。If a server name is specified but no database name is specified, the SQL Server 数据库引擎SQL 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.

默认情况下,参数可为空值。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* 可以为任何 *char*、*varchar*、*nchar* 或 *nvarchar* 数据类型。 @*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.

LoginLOGIN
适用范围: 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.

UserUSER
指定要模拟的上下文是当前数据库中的用户。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

指定对 linked_server_name 执行 command_string,并将结果(如果有)返回到客户端。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.

保证执行语句不返回任何结果。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.

当一个模块调用其他模块或通过引用公共语言运行时 (CLR) 模块、用户定义类型或聚合执行托管代码时,将出现嵌套。Nesting occurs when one module calls another or executes managed code by referencing a common language runtime (CLR) module, user-defined type, or aggregate. 当开始执行调用模块或托管代码引用时,嵌套级别将增加,而当调用模块或托管代码引用完成时,嵌套级别将减少。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 级,超过 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_. 这些存储过程物理上存储在资源数据库中,但逻辑上出现在每个系统数据库和用户定义数据库的 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.

使用带字符串的 EXECUTE 命令Using 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_principals 的主体,否则该语句将失败。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.

PermissionsPermissions

运行 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

uspGetEmployeeManagers 数据库中的 AdventureWorks2012AdventureWorks2012 存储过程需要一个参数 (@EmployeeID)。The uspGetEmployeeManagers stored procedure in the AdventureWorks2012AdventureWorks2012 database expects one parameter (@EmployeeID). 以下示例执行uspGetEmployeeManagers 存储过程,以 Employee ID 6 作为参数值。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. 该存储过程将传递两个参数:第一个参数为产品 ID (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. 使用带 DEFAULT 的 EXECUTEUsing 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. 使用带 AT linked_server_name 的 EXECUTEUsing 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. 对用户定义函数使用 EXECUTEUsing EXECUTE with a user-defined function

下面的示例在 ufnGetSalesOrderStatusText 数据库中执行 AdventureWorks2012AdventureWorks2012 标量用户定义函数。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 Engine将检查 User1 的权限。The 数据库引擎Database 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_name 使用参数Using 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

以下示例创建一个包含参数的过程,并演示执行该过程的三种方法: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
主体(数据库引擎) 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