执行存储过程

  • 项目

适用于：SQL ServerAzure SQL 数据库Azure SQL 托管实例Azure Synapse AnalyticsAnalytics Platform System (PDW)

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

有不同方法执行存储过程。 第一种方法和最常见的方法供应用程序或用户调用过程。 另一种方法是将存储过程设置为在启动 SQL Server 实例时自动运行。

当应用程序或用户调用过程时，调用中显式声明了 Transact-SQL EXECUTE 或 EXEC 关键字。 如果过程是 Transact-SQL 批处理中的第一条语句，那么不使用关键字 EXEC 也可调用并执行此过程。

限制和局限

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

EXEC SP_heLP; -- Fails to resolve because SP_heLP doesn't equal sp_help

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

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

建议

使用以下建议来执行存储过程。

系统存储过程

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

EXEC sys.sp_who;

用户定义的存储过程

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

以下示例演示执行用户定义过程的推荐方法。 此过程接受两个输入参数。 有关指定输入参数和输出参数的信息，请参阅在存储过程中指定参数。

EXECUTE SalesLT.uspGetCustomerCompany @LastName = N'Cannon', @FirstName = N'Chris';
GO

或：

EXEC SalesLT.uspGetCustomerCompany N'Cannon', N'Chris';
GO

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

  1. sys 架构。

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

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

安全性

有关安全信息，请参阅 EXECUTE AS (Transact-SQL) 和 EXECUTE AS 子句 (Transact-SQL)。

权限

有关权限的信息，请参阅执行 (Transact-SQL)中的权限。

存储过程执行

可以在 SSMS 查询窗口中使用 SQL Server Management Studio （SSMS） 用户界面或 Transact-SQL 来执行存储过程。 始终使用最新版本的 SSMS。

使用 SQL Server Management Studio

  1. 在“对象资源管理器”中，连接到 SQL Server 或 Azure SQL 数据库的实例，展开该实例，然后展开“数据库”。

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

  3. 右键单击想要运行的存储过程，然后选择“执行存储过程”。

  4. 在“执行过程”对话框中，“参数”表示每个参数的名称、“数据类型”表示其数据类型，“输出参数”表示其是否为输出参数。

    对于每个参数：

    • 在“值”下，键入要用于参数的值。
    • 在“传递空值”下，选择是否将 NULL 作为参数值传递。

  5. 选择“确定”执行存储过程。 如果存储过程不具有任何参数，只需选择“确定”。

    存储过程将运行，结果将显示在“结果”窗格中。

    例如，要运行创建存储过程一文中的 SalesLT.uspGetCustomerCompany 存储过程，请输入 Cannon 作为 @LastName 参数，输入 Chris 作为 @FirstName 参数，然后选择“确定”。 该过程将返回 FirstNameChris、LastNameCannon 和 CompanyNameOutdoor Sporting Goods。

在查询窗口中使用 Transact-SQL

  1. 在 SSMS 中，连接到 SQL Server 或 Azure SQL 数据库的实例。

  2. 从工具栏中选择“新建询问”。

  3. 在查询窗口中输入具有以下语法的 EXECUTE 语句，并提供所有预期参数的值：

    EXECUTE <ProcedureName> N'<Parameter 1 value>, N'<Parameter x value>;  
GO

    例如，以下 Transact-SQL 语句执行 uspGetCustomerCompany 存储过程，将 Cannon 作为 @LastName 参数值，将 Chris 作为 @FirstName 参数值：

    EXEC SalesLT.uspGetCustomerCompany N'Cannon', N'Chris';
GO

  4. 在工具栏中选择“执行”。 存储过程将运行。

参数值选项

可通过多种方式在存储过程 EXECUTE 语句中提供参数和值。 以下示例显示了 EXECUTE 语句的几个不同选项。

  • 如果按照存储过程中定义的相同顺序提供参数值，则无需说明参数名称。 例如：

    EXEC SalesLT.uspGetCustomerCompany N'Cannon', N'Chris';

  • 如果在 @parameter_name=value 模式中提供参数名称，则无需按照定义的相同顺序指定参数名称和值。 例如，以下任一语句均有效：

    EXEC SalesLT.uspGetCustomerCompany @FirstName = N'Chris', @LastName = N'Cannon';

    或：

    EXEC SalesLT.uspGetCustomerCompany @LastName = N'Cannon', @FirstName = N'Chris';

  • 如果将 @parameter_name=value 形式用于任何参数，则必须将其用于该语句中的所有后续参数。 例如，您无法使用 EXEC SalesLT.uspGetCustomerCompany1 @FirstName = N'Chris', N'Cannon';。

启动时自动执行

适用范围：SQL Server

在 SQL Server 中，sysadmin 服务器角色的成员可以使用 sp_procoption 来设置或清除在启动时自动执行的过程。 启动过程必须位于 master 数据库中，并且不能包含输入或输出参数。 有关详细信息，请参阅 sp_procoption (Transact-SQL)。

在每次启动 SQL Server 时将执行标记为自动执行的过程，并在启动过程期间中恢复 master 数据库。 将这些过程设置为自动执行对执行数据库维护操作或使这些过程作为后台进程连续运行很有用。

自动执行的另一个用途是使该过程执行 tempdb 中的系统或维护任务，如创建一个全局临时表。 自动执行可确保在 SQL Server 启动过程中重新创建 tempdb 时，始终存在这样一个临时表。

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

虽然对启动过程的数目没有限制，但在执行时每个启动过程将占用一个工作线程。 如果需要在启动时执行多个过程，但不需要并行执行，则可以指定一个过程作为启动过程，让该过程调用其他过程。 此方法只占用一个工作线程。

提示

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

注意

Azure SQL 数据库设计为将功能与 master 数据库上的依赖项相隔离。 因此，配置服务器级选项的 Transact-SQL 语句在 Azure SQL 中不可用。 在弹性作业（预览版）或 Azure 自动化等其他 Azure 服务中，通常都能找到合适的替代方法。

设置在启动时自动执行的过程

只有系统管理员 (sa) 可以将过程标记为自动执行。

  1. 在 SSMS 中，连接到数据库引擎。

  2. 在标准工具栏中，选择“新建查询”。

  3. 输入以下 sp_procoption 命令，将存储过程设置为在 SQL Server 启动时自动执行。

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

  4. 在工具栏中，选择“执行”。

阻止在启动时自动执行过程

A sysadmin 可以使用 sp_procoption 来停止在 SQL Server 启动时自动执行的过程。

  1. 在 SSMS 中，连接到数据库引擎。

  2. 在标准工具栏中，选择“新建查询”。

  3. 将以下命令输入到查询窗口中。

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

  4. 在工具栏中，选择“执行”。

相关内容