执行存储过程

适用于: 是SQL Server(所有支持的版本) 是Azure SQL 数据库 是Azure SQL 托管实例 是Azure Synapse Analytics 是并行数据仓库

本文介绍如何使用或在中执行存储过程 SQL Server SQL Server Management Studio Transact-SQL 。

有两种不同方法执行存储过程。 第一种方法和最常见的方法供应用程序或用户调用过程。 第二种方法是将过程设置为在启动 SQL Server 实例时自动运行。 当应用程序或用户调用过程时,调用中显式声明了 Transact-SQL EXECUTE 或 EXEC 关键字。 如果过程是批处理中的第一个语句,则可以调用并执行无 EXEC 关键字的过程 Transact-SQL 。

准备工作

限制和局限

与系统过程名称匹配时使用调用数据库排序规则。 出于此原因,在过程调用中始终使用系统过程名称的正确大小写形式。 例如,如果在具有区分大小写排序规则的数据库上下文中执行,则此代码将失败:

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

若要显示确切的系统过程名称,请查询 sys.system_objectssys.system_parameters 目录视图。

如果用户定义的过程与系统过程同名,则可能不会执行用户定义的过程。

建议

执行系统存储过程

系统过程以前缀开头 sp_ 。 因为从逻辑意义上讲,这些过程出现在所有用户定义的数据库和系统定义的数据库中,所以可以从任何数据库执行这些过程,而不必完全限定过程名称。 但是,我们建议对所有系统过程名称进行架构限定, sys 以防止名称冲突。 以下示例说明调用系统过程的推荐方法。

EXEC sys.sp_who;  

执行用户定义的存储过程

当执行用户定义的过程时,我们建议使用架构名称限定过程名称。 这种做法使性能得到小幅提升,因为 数据库引擎 不必搜索多个架构。 如果某个数据库在多个架构中具有同名过程,则这还可以防止执行错误的过程。

以下示例说明执行用户定义过程的推荐方法。 请注意,此过程接受一个输入参数。 有关指定输入参数和输出参数的信息,请参阅 指定参数

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

-或-

EXEC AdventureWorks2019.dbo.uspGetEmployeeManagers 50;  
GO  

如果指定了非限定的用户定义过程,则 数据库引擎 按以下顺序搜索此过程:

  1. sys当前数据库的架构。

  2. 调用方的默认架构(如果它在批处理或动态 SQL 中执行)。 或者,如果非限定的过程名称出现在另一个过程定义的主体中,则接着搜索包含后一过程的架构。

  3. dbo当前数据库中的架构。

自动执行存储过程

标记为自动执行的过程将在每次启动时执行 SQL Server ,并且在 master 启动过程中恢复数据库。 将这些过程设置为自动执行对执行数据库维护操作或使这些过程作为后台进程连续运行很有用。 自动执行的另一个用途是使过程在中执行系统或维护任务 tempdb ,例如创建一个全局临时表。 这可以确保在 tempdb 启动过程中重新创建时,此类临时表将始终存在 SQL Server 。

自动执行的过程使用与固定服务器角色 sysadmin 的成员相同的权限进行操作。 该过程生成的所有错误消息都将写入 SQL Server 错误日志。

虽然对启动过程的数目没有限制,但是请注意,在执行时每个启动过程将占用一个工作线程。 如果必须在启动时执行多个过程,但不需要并行执行,则可以指定一个过程作为启动过程,让该过程调用其他过程。 这样就只占用一个工作线程。

提示

请勿从自动执行的过程中返回任何结果集。 因为该过程是由 SQL Server 而不是由应用程序或用户执行的,所以结果集将无处可去。

设置、清除和控制自动执行

只有系统管理员 (sa) 可以将过程标记为自动执行。 此外,该过程必须位于 master 数据库中(由拥有 sa ),并且不能有输入或输出参数。

使用 sp_procoption 可以:

  1. 将现有过程指定为启动过程。

  2. 阻止过程在 SQL Server 启动时执行。

Security

有关详细信息,请参阅 EXECUTE AS (Transact-SQL)EXECUTE AS 子句 (Transact-SQL)

权限

有关详细信息,请参阅 EXECUTE (Transact-SQL)中执行存储过程。

使用 SQL Server Management Studio

执行存储过程

  1. “对象资源管理器” 中,连接到 SQL Server 数据库引擎的实例,再依次展开该实例、 “数据库”

  2. 展开所需的数据库,然后依次展开 “可编程性”“存储过程”

  3. 右键单击所需的用户定义存储过程,然后选择 " 执行存储过程"。

  4. “执行过程” 对话框中,为每个参数指定一个值以及它是否应传递 Null 值。

    Parameter
    指示参数的名称。

    数据类型
    指示参数的数据类型。

    输出参数
    指示是否为输出参数。

    传递空值
    将 NULL 作为参数值传递。


    在调用过程时键入参数的值。

  5. 若要执行存储过程,请选择 "确定"

使用 Transact-SQL

执行存储过程

  1. 连接到 数据库引擎。

  2. 在标准栏上,选择“新建查询”。

  3. 将以下示例复制并粘贴到查询窗口中,然后选择“执行”。 此示例演示如何执行应有一个参数的存储过程。 该示例执行 uspGetEmployeeManagers 存储过程,并将值6指定为 @EmployeeID 参数。

USE AdventureWorks2019;  
GO  
EXEC dbo.uspGetEmployeeManagers 6;  
GO  

设置或清除自动执行的过程

启动过程必须在数据库中 master ,并且不能包含输入参数或输出参数。 所有数据库恢复后将开始执行存储过程,并在开始时记录“恢复已完成”消息。

有关详细信息,请参阅 sp_procoption (Transact-SQL)

  1. 连接到 数据库引擎。

  2. 在标准栏上,选择“新建查询”。

  3. 将以下示例复制并粘贴到查询窗口中,然后选择“执行”。 此示例演示如何使用 sp_procoption 设置过程自动执行。

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

阻止过程自动执行

  1. 连接到 数据库引擎。

  2. 在标准栏上,选择“新建查询”。

  3. 将以下示例复制并粘贴到查询窗口中,然后选择“执行”。 此示例说明如何使用 sp_procoption 阻止过程自动执行。

EXEC sp_procoption @ProcName = N'<procedure name>'      
    , @OptionName = 'startup'
    , @OptionValue = 'off';
GO

后续步骤