执行存储过程Execute a Stored Procedure

适用对象: yesSQL ServeryesAzure SQL 数据库yesAzure SQL 数据仓库yes并行数据仓库APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure SQL Data Warehouse yesParallel Data Warehouse

本主题介绍如何使用 SQL Server 2017SQL Server 2017SQL Server Management StudioSQL Server Management StudioTransact-SQLTransact-SQL中执行存储过程。This topic describes how to execute a stored procedure in SQL Server 2017SQL Server 2017 by using SQL Server Management StudioSQL Server Management Studio or Transact-SQLTransact-SQL.

有两种不同方法执行存储过程。There are two different ways to execute a stored procedure. 第一种方法和最常见的方法供应用程序或用户调用过程。The first and most common approach is for an application or user to call the procedure. 第二种方法是将过程设置为在启动 SQL ServerSQL Server 实例时自动运行。The second approach is to set the procedure to run automatically when an instance of SQL ServerSQL Server starts. 当应用程序或用户调用过程时,调用中显式声明了 Transact-SQLTransact-SQL EXECUTE 或 EXEC 关键字。When a procedure is called by an application or user, the Transact-SQLTransact-SQL EXECUTE or EXEC keyword is explicitly stated in the call. 或者,如果过程是 Transact-SQLTransact-SQL 批处理中的第一条语句,那么不使用关键字也可以调用并执行此过程。Alternatively, the procedure can be called and executed without the keyword if the procedure is the first statement in the Transact-SQLTransact-SQL batch.

本主题内容In This Topic

开始之前Before You Begin

限制和局限Limitations and Restrictions

  • 与系统过程名称匹配时使用调用数据库排序规则。The calling database collation is used when matching system procedure names. 因此,在过程调用中始终使用系统过程名称的正确大小写形式。Therefore, always use the exact case of system procedure names in procedure calls. 例如,如果在具有区分大小写的排序规则的数据库上下文中执行,以下代码将失败:For example, this code will fail if it is executed in the context of a database that has a case-sensitive collation:

    EXEC SP_heLP; -- Will fail to resolve because SP_heLP does not equal sp_help  
    

    若要显示确切的系统过程名称,请查询 sys.system_objectssys.system_parameters 目录视图。To display the exact system procedure names, query the sys.system_objects and sys.system_parameters catalog views.

  • 如果用户定义的过程与系统过程同名,则可能不会执行用户定义的过程。If a user-defined procedure has the same name as a system procedure, the user-defined procedure might not ever execute.

建议Recommendations

  • 执行系统存储过程Executing System Stored Procedures

    系统过程以前缀 sp_ 开头。System procedures begin with the prefix sp_. 因为从逻辑意义上讲,这些过程出现在所有用户定义的数据库和系统定义的数据库中,所以可以从任何数据库执行这些过程,而不必完全限定过程名称。Because they logically appear in all user- and system- defined databases, they can be executed from any database without having to fully qualify the procedure name. 但是,建议使用 sys 架构名称对所有系统过程名称进行架构限定,以防止名称冲突。However, we recommend schema-qualifying all system procedure names with the sys schema name to prevent name conflicts. 以下示例说明调用系统过程的推荐方法。The following example demonstrates the recommended method of calling a system procedure.

    EXEC sys.sp_who;  
    
  • 执行用户定义存储过程Executing User-defined Stored Procedures

    当执行用户定义的过程时,我们建议使用架构名称限定过程名称。When executing a user-defined procedure, we recommend qualifying the procedure name with the schema name. 这种做法使性能得到小幅提升,因为 数据库引擎Database Engine 不必搜索多个架构。This practice gives a small performance boost because the 数据库引擎Database Engine does not have to search multiple schemas. 如果某个数据库在多个架构中具有同名过程,则这还可以防止执行错误的过程。It also prevents executing the wrong procedure if a database has procedures with the same name in multiple schemas.

    以下示例说明执行用户定义过程的推荐方法。The following example demonstrates the recommended method to execute a user-defined procedure. 请注意,此过程接受一个输入参数。Notice that the procedure accepts one input parameter. 有关指定输入参数和输出参数的信息,请参阅 指定参数For information about specifying input and output parameters, see Specify Parameters.

    USE AdventureWorks2012;  
    GO  
    EXEC dbo.uspGetEmployeeManagers @BusinessEntityID = 50;  
    

    -或--Or-

    EXEC AdventureWorks2012.dbo.uspGetEmployeeManagers 50;  
    GO  
    

    如果指定了非限定的用户定义过程,则 数据库引擎Database Engine 按以下顺序搜索此过程:If a nonqualified user-defined procedure is specified, the 数据库引擎Database Engine searches for the procedure in the following order:

    1. 当前数据库的 sys 架构。The sys schema of the current database.

    2. 调用方的默认架构(如果它在批处理或动态 SQL 中执行)。The caller's default schema if it is executed in a batch or in dynamic SQL. 或者,如果非限定的过程名称出现在另一个过程定义的主体中,则接着搜索包含后一过程的架构。Or, if the nonqualified procedure name appears inside the body of another procedure definition, the schema that contains this other procedure is searched next.

    3. 当前数据库中的 dbo 架构。The dbo schema in the current database.

  • 自动执行存储过程Executing Stored Procedures Automatically

    在每次启动 SQL ServerSQL Server 时将执行标记为自动执行的过程,并在启动过程期间中恢复 master 数据库。Procedures marked for automatic execution are executed every time SQL ServerSQL Server starts and the master database is recovered during that startup process. 将这些过程设置为自动执行对执行数据库维护操作或使这些过程作为后台进程连续运行很有用。Setting up procedures to execute automatically can be useful for performing database maintenance operations or for having procedures run continuously as background processes. 自动执行的另一个用途是使该过程执行 tempdb中的系统或维护任务,如创建一个全局临时表。Another use for automatic execution is to have the procedure perform system or maintenance tasks in tempdb, such as creating a global temporary table. 这将确保在 启动过程中重新创建 tempdb SQL ServerSQL Server 时,始终存在这样一个临时表。This makes sure that such a temporary table will always exist when tempdb is re-created during SQL ServerSQL Server startup.

    自动执行的过程使用与固定服务器角色 sysadmin 的成员相同的权限进行操作。A procedure that is automatically executed operates with the same permissions as members of the sysadmin fixed server role. 该过程生成的所有错误消息都将写入 SQL ServerSQL Server 错误日志。Any error messages generated by the procedure are written to the SQL ServerSQL Server error log.

    虽然对启动过程的数目没有限制,但是请注意,在执行时每个启动过程将占用一个工作线程。There is no limit to the number of startup procedures you can have, but be aware that each consumes one worker thread while executing. 如果必须在启动时执行多个过程,但不需要并行执行,则可以指定一个过程作为启动过程,让该过程调用其他过程。If you must execute multiple procedures at startup but do not need to execute them in parallel, make one procedure the startup procedure and have that procedure call the other procedures. 这样就只占用一个工作线程。This uses only one worker thread.

    提示

    请勿从自动执行的过程中返回任何结果集。Do not return any result sets from a procedure that is executed automatically. 因为该过程是由 SQL ServerSQL Server 而不是由应用程序或用户执行的,所以结果集将无处可去。Because the procedure is being executed by SQL ServerSQL Server instead of an application or user, there is nowhere for the result sets to go.

  • 设置、清除和控制自动执行Setting, Clearing, and Controlling Automatic Execution

    只有系统管理员 (sa) 可以将过程标记为自动执行。Only the system administrator (sa) can mark a procedure to execute automatically. 另外,该过程必须在 master 数据库中,由 sa所有,而且不能有输入或输出参数。In addition, the procedure must be in the master database, owned by sa, and cannot have input or output parameters.

    使用 sp_procoption 可以:Use sp_procoption to:

    1. 将现有过程指定为启动过程。Designate an existing procedure as a startup procedure.

    2. 阻止过程在 SQL ServerSQL Server 启动时执行。Stop a procedure from executing at SQL ServerSQL Server startup.

SecuritySecurity

有关详细信息,请参阅 EXECUTE AS (Transact-SQL)EXECUTE AS 子句 (Transact-SQL)For more information, see EXECUTE AS (Transact-SQL) and EXECUTE AS Clause (Transact-SQL).

权限Permissions

有关详细信息,请参阅 EXECUTE (Transact-SQL)中执行存储过程。For more information, see the "Permissions" section in EXECUTE (Transact-SQL).

使用 SQL Server Management StudioUsing SQL Server Management Studio

执行存储过程To execute a stored procedure

  1. “对象资源管理器” 中,连接到 SQL Server 数据库引擎SQL Server Database Engine的实例,再依次展开该实例、 “数据库”In Object Explorer, connect to an instance of the SQL Server 数据库引擎SQL Server Database Engine, expand that instance, and then expand Databases.

  2. 展开所需的数据库,然后依次展开 “可编程性”“存储过程”Expand the database that you want, expand Programmability, and then expand Stored Procedures.

  3. 右键单击所需的用户定义存储过程,然后单击“执行存储过程” 。Right-click the user-defined stored procedure that you want and click Execute Stored Procedure.

  4. “执行过程” 对话框中,为每个参数指定一个值以及它是否应传递 Null 值。In the Execute Procedure dialog box, specify a value for each parameter and whether it should pass a null value.

    参数Parameter
    指示参数的名称。Indicates the name of the parameter.

    数据类型Data Type
    指示参数的数据类型。Indicates the data type of the parameter.

    输出参数Output Parameter
    指示是否为输出参数。Indicates if this is an output parameter.

    传递空值Pass Null Value
    将 NULL 作为参数值传递。Pass a NULL as the value of the parameter.

    ReplTest1Value
    在调用过程时键入参数的值。Type the value for the parameter when calling the procedure.

  5. 若要执行存储过程,请单击 “确定”To execute the stored procedure, click OK.

使用 Transact-SQLUsing Transact-SQL

执行存储过程To execute a stored procedure

  1. 连接到 数据库引擎Database EngineConnect to the 数据库引擎Database Engine.

  2. 在标准菜单栏上,单击 “新建查询”From the Standard bar, click New Query.

  3. 将以下示例复制并粘贴到查询窗口中,然后单击“执行” 。Copy and paste the following example into the query window and click Execute. 此示例演示如何执行应有一个参数的存储过程。This example shows how to execute a stored procedure that expects one parameter. 该示例执行 uspGetEmployeeManagers 存储过程,并将值 6 指定为 @EmployeeID 参数。The example executes the uspGetEmployeeManagers stored procedure with the value 6 specified as the @EmployeeID parameter.

USE AdventureWorks2012;  
GO  
EXEC dbo.uspGetEmployeeManagers 6;  
GO  

设置或清除过程自动执行To set or clear a procedure for executing automatically

  1. 连接到 数据库引擎Database EngineConnect to the 数据库引擎Database Engine.

  2. 在标准菜单栏上,单击 “新建查询”From the Standard bar, click New Query.

  3. 将以下示例复制并粘贴到查询窗口中,然后单击“执行” 。Copy and paste the following example into the query window and click Execute. 此示例演示如何使用 sp_procoption 设置过程自动执行。This example shows how to use sp_procoption to set a procedure for automatic execution.

USE AdventureWorks2012;  
GO  
EXEC sp_procoption @ProcName = '<procedure name>'   
    , @OptionName = ] 'startup'   
    , @OptionValue = 'on';  

阻止过程自动执行To stop a procedure from executing automatically

  1. 连接到 数据库引擎Database EngineConnect to the 数据库引擎Database Engine.

  2. 在标准菜单栏上,单击 “新建查询”From the Standard bar, click New Query.

  3. 将以下示例复制并粘贴到查询窗口中,然后单击“执行” 。Copy and paste the following example into the query window and click Execute. 此示例说明如何使用 sp_procoption 阻止过程自动执行。This example shows how to use sp_procoption to stop a procedure from executing automatically.

USE AdventureWorks2012;  
GO  
EXEC sp_procoption @ProcName = '<procedure name>'   
    , @OptionValue = 'off';  

示例 (Transact-SQL)Example (Transact-SQL)

另请参阅See Also

指定参数 Specify Parameters
配置 scan for startup procs 服务器配置选项 Configure the scan for startup procs Server Configuration Option
EXECUTE (Transact-SQL) EXECUTE (Transact-SQL)
CREATE PROCEDURE (Transact-SQL) CREATE PROCEDURE (Transact-SQL)
存储过程(数据库引擎)Stored Procedures (Database Engine)