SQL sever中的存储过程

向右看齐 2024-03-27 19:16 86阅读 0赞

> **在Oracle的专篇中我也有仔细总结了存储过程的相关内容，**
> 
> 文章链接：[http://t.csdnimg.cn/Z8AnH][http_t.csdnimg.cn_Z8AnH]
> 
> **尽管Oracle和SQL sever之间是存在一些区别，但许多基本的概念和原则在Oracle和SQL Server之间是通用的。它们之间有一些常见的区别，如下所述：**
> 
> 1.  语法差异：两个数据库系统在存储过程的语法方面存在差异。例如，存储过程的声明、变量声明、控制结构和异常处理等方面可能会有不同的语法。
> 2.  参数传递方式：在Oracle中，存储过程可以使用IN、OUT和IN OUT类型的参数来进行数据传递。而在SQL Server中，可以使用输入参数、输出参数和表值参数来实现类似的功能。
> 3.  游标处理：Oracle和SQL Server在游标处理方面也有一些差异。例如，在Oracle中，可以使用隐式游标或显式游标来处理结果集。而在SQL Server中，使用游标需要显式声明和操作。
> 4.  系统函数和特性：每个数据库系统都有自己特定的系统函数和特性，这些函数和特性可以在存储过程中使用。因此，在编写存储过程时，可能需要根据目标数据库系统的要求来选择合适的函数和特性。
> 
> **下面内容我将总结描述下在SQL sever2008版本数据库中的存储过程，可以对比参照Oracle的内容进行理解掌握。**

**目录**

一、存储过程概念

二、存储过程组成

三、存储过程优缺

3.1优点：

3.2缺点：

四、创建存储过程

4.1使用向导创建存过

4.2使用CREATE PROC语句创建存储过程

五、管理存储过程

5.1执行存储过程

5.2查看存储过程

5.2.1.使用sys.sql\_modules查看存储过程的定义

5.2.2.使用OBJECT\_DEFINITION查看存储过程的定义

5.2.3.使用sp\_helptext查看存储过程的定义

5.3修改存储过程

5.3.1.ALTER PROCEDURE语句

5.4重命名存储过程

5.4.1.手动操作重新命名存储过程

5.4.2.执行sp\_rename系统存储过程重新命名存储过程

5.5删除存储过程

5.5.1手动删除存储过程

5.5.2.执行DROP PROCEDURE语句删除存储过程

六、存过创建与调试步骤

--------------------

## 一、存储过程概念 ##

存储过程(Stored Procedure)是预编译SQL语句的集合，这些语句存储在一个名称下并作为一个单元来处理。存储过程代替了传统的逐条执行SQL语句的方式。一个存储过程中可包含查询、插入、删除、更新等操作的一系列SQL语句，当这个存储过程被调用执行时，这些操作也会同时执行。  
存储过程与其他编程语言中的过程类似，它可以接受输入参数并以输出参数的格式向调用过程或批处理返回多个值；包含用于在数据库中执行操作（包括调用其他过程）的编程语句：向调用过程或批处理返回状态值，以指明成功或失败（以及失败的原因）。通过输入参数和输出参数与应用程序进行交互。  
SQL Server提供了3种类型的存储过程，各类型存储过程如下：

*  ☑系统存储过程：用来管理SOL Server和显示有关数据库和用户信息的存储过程。
 *  ☑自定义存储过程：用户在SQL Server中通过采用SQL语句创建存储过程。
 *  ☑扩展存储过程：通过编程语言（如C语言）创建外部例程，并将这个例程在SQL Server中作
 *  为存储过程使用。

实际上，存储过程几乎为所有的SQL Server应用提供了支持以及数据库接口。它们被保存在数据库中并分配一个名称，以供以后重复使用，提供了一种有效的方式来组织和重复使用SQL代码，以及实现更复杂的业务逻辑。

## 二、存储过程组成 ##

存储过程通常由以下几个部分组成：

*  1. \*\*头部（Header）\*\*：

存储过程的头部包含了定义存储过程的名称、参数和返回值类型。它以\`CREATE PROCEDURE\`语句开始，后面紧跟着存储过程的名称和参数列表。这些参数可以是输入参数、输出参数或者既是输入又是输出的参数。

*  2. \*\*参数（Parameters）\*\*：

存储过程可以接受零个或多个参数。参数是可选的，用于传递输入值给存储过程并返回输出结果。每个参数都有一个名称、数据类型和方向（输入、输出或输入/输出）。

*  3. \*\*主体（Body）\*\*：

存储过程的主体是实际执行的代码块，它包含了存储过程的逻辑操作和SQL语句，用于实现具体的功能。主体部分位于\`BEGIN\`和\`END\`关键字之间，并且可以包含条件语句、循环结构、异常处理和事务控制等控制流程和业务逻辑。可以在主体中使用流程控制语句（例如IF、WHILE）和SQL查询语句（例如SELECT、UPDATE）等。

*  4. \*\*局部变量（Local Variables）\*\*：

存储过程可以声明局部变量，这些变量只在存储过程内部有效。局部变量用于存储中间结果、临时数据或计算结果，并可以在存储过程的主体部分使用。

*  5. \*\*异常处理（Exception Handling）\*\*：

存储过程可以使用异常处理机制来捕获和处理可能发生的错误或异常情况。通过使用\`TRY...CATCH\`语句块，可以在存储过程中定义一组处理异常的操作。

*  6. \*\*事务控制（Transaction Control）\*\*：

存储过程可以使用事务来确保一系列操作的原子性，并在必要时进行回滚或提交。通过使用\`BEGIN TRANSACTION\`、\`COMMIT\`和\`ROLLBACK\`等语句，可以在存储过程中控制事务的边界。

*  7. \*\*权限和安全性（Permissions and Security）\*\*：

存储过程可以被授予特定用户或角色的执行权限。数据库管理员可以根据需求设置存储过程的访问权限，以确保数据的安全性和合规性。

## 三、存储过程优缺 ##

### 3.1优点： ###

1.  **提高性能**：存储过程是预编译的，一旦创建，其执行计划会被缓存，从而提高了查询的执行速度。存储过程的编译结果被缓存，以便在后续执行中重复使用。这可以减少每次执行存储过程时的开销，从而提高查询和数据操作的性能。
2.  **代码复用**：存储过程使得可以将常用的业务逻辑封装在一个可重用的单元中。这样可以避免重复编写相同的SQL代码，并更方便地进行维护和修改。这样存储过程可以在多个应用程序中重复使用，从而减少代码的冗余并提高开发效率。那么就可以减少错误和维护成本，并促进代码的一致性和可维护性。
3.  **安全性**：存储过程可以通过权限控制来限制用户对数据的访问和操作。只有具有足够权限的用户才能执行存储过程，从而保护敏感数据和防止未经授权的访问。
4.  **简化复杂查询**：存储过程可以包含多个SQL语句和逻辑操作，使得对复杂查询的处理更加简单和可维护。它们可以将复杂的业务逻辑封装在一个单独的存储过程中，使代码更具可读性和可管理性。
5.  **减少网络流量**：通过将SQL语句封装在存储过程中，可以减少与数据库服务器之间传输的数据量。因为只需传递存储过程的名称和参数，而不是完整的SQL查询语句。存储过程可以在远程客户端上执行，并仅将结果返回给客户端，从而减少了网络流量。
6.  **事务控制**：存储过程可以使用事务控制语句（如BEGIN TRANSACTION、COMMIT TRANSACTION和ROLLBACK TRANSACTION）来确保数据的一致性和完整性。通过将多个数据库操作放在同一个存储过程中，可以更好地管理和控制事务。

### 3.2缺点： ###

1.  **学习和开发成本**：编写和维护存储过程需要一定的学习和开发成本。因为存储过程是使用特定的编程语言（如T-SQL）编写的，开发人员需要熟悉该语言的语法和特性。编写和调试存储过程可能需要更多的时间和努力，特别是对于不熟悉存储过程的开发人员。
2.  **难以维护**：存储过程的复杂性增加了维护的难度。由于存储过程通常包含大量的代码和逻辑，因此当需要进行更改或修复时，可能需要花费更多的时间和精力来理解和修改存储过程。特别当出现问题时，调试可能会更加困难。
3.  **版本控制**：存储过程的版本控制可能比较困难。如果多个开发人员同时对存储过程进行更改，那么很容易出现冲突和合并问题。因此，必须采取适当的版本控制策略和实践来管理存储过程的演变和改变。
4.  **数据库依赖**：存储过程通常直接依赖于数据库结构和对象。存储过程的语法和功能可能会因不同的数据库版本而有所变化。如果数据库结构发生变化，可能需要相应地修改和更新存储过程。这种紧密的耦合可能导致维护的复杂性和风险。存储过程是特定于数据库管理系统（DBMS）的，因此在将应用程序从一个DBMS迁移到另一个DBMS时，可能需要重写或调整存储过程。在升级或迁移数据库时，存储过程可能需要进行更新或调整。

## 四、创建存储过程 ##

存储过程(Stored Procedure)是在数据库服务器端执行的一组T-SQL语句的集合，经编译后存放  
在数据库服务器中。下面主要介绍如何通过企业管理器和Transact-.SQL语句创建存储过程。

### 4.1使用向导创建存过 ###

在SQL Server2008中，使用向导创建存储过程的步骤如下：

(1)启动SQL Server Management Studio,并连接到SQL Server2008中的数据库。

(2)在“对象资源管理器”中选择指定的服务器和数据库，展开数据库的“可编程性”节点，右  
击“存储过程”，在弹出的快捷菜单中选择“新建存储过程”命令，如图所示。

![1c14d81cee7149e097b99d9a1f54c0c1.png][]  
(3)在弹出的“连接到数据库引擎”窗口中，单击“连接”按钮，便出现创建存储过程的窗口，  
如图所示。

![41e518c56b854ef68cf1c39a13d52b6c.png][]

在存储过程窗口的文本框中，可以看到系统自动给出了创建存储过程的格式模板语句，可对工具  
模板格式进行修改来创建新的存储过程。

> **注释：**
> 
> ANSI\_NULLS 和 QUOTED\_IDENTIFIER 选项。它们具有以下含义：
> 
>  *  `SET ANSI_NULLS ON`：将 ANSI\_NULLS 设置为 ON，表示启用 ANSI 标准中关于 NULL 值的比较规则。当此选项打开时，NULL 的比较会被认为是未知的，并且在 WHERE 子句中使用 "column\_name = NULL" 时不会返回任何结果。相反，应该使用 "column\_name IS NULL" 来检查是否为 NULL 值。
>  *  `SET QUOTED_IDENTIFIER ON`：将 QUOTED\_IDENTIFIER 设置为 ON，表示启用标识符引号的使用。当此选项打开时，可以使用双引号（"）来引用标识符，例如表名或列名，而不仅限于方括号（\[\]）。这样可以避免某些情况下的歧义和错误，尤其是在使用保留字或特殊字符作为标识符时。
> 
> 使用 `GO` 关键字将这些命令分隔成多个批处理。每个批处理在执行时都会单独运行，并且在一个批处理之间会存在一定的上下文切换。
> 
> ***注意***：这两个语句的解析并不涉及实际的逻辑操作或查询。它们只是用于设置数据库的选项和配置，以确保在后续的存储过程或脚本中使用正确的规则和行为。
> 
>     -- =============================================
>     -- Author:		<Author,,Name>   --存过创建编辑人员
>     -- Create date: <Create Date,,>  --创建日期
>     -- Description:	<Description,,>  --描述错过的作用
>     -- =============================================
> 
> **提示：编写存过一定要培养好的习惯，对自己编写的代码做好注释，描述关键的代码逻辑信息，为后续代码修改维护提供良好的帮助。**

【例】创建一个名称为Proc\_Stu的存储过程，要求完成以下功能：在Student表中查询男生的Sno、Sex、Sage这几个字段的内容。具体的操作步骤如下：

提前准备student表，语句如下：

use SJCX ;
    
    -- 创建 Student 表
    CREATE TABLE Student (
        Sno INT PRIMARY KEY,
        Sname NVARCHAR(50),
        Sex NVARCHAR(10),
        Sage INT,
        Grade NVARCHAR(20)
    );
    
    -- 向 Student 表插入5条数据
    INSERT INTO Student (Sno, Sname, Sex, Sage, Grade)
    VALUES
        (1, 'Amy', 'Female', 20, 'A'),
        (2, 'Ben', 'Male', 22, 'B'),
        (3, 'Chris', 'Male', 21, 'A'),
        (4, 'Diana', 'Female', 19, 'C'),
        (5, 'Eric', 'Male', 20, 'B');
    
    SELECT Sno,Sname,Sex,Sage
    FROM Student
    WHERE Sex = 'Male';

(1)在创建存储过程的窗口中选择“查询”菜单中的“指定模板参数的值”命令，弹出“指定模板参数的值”对话框，如图所示。

![563787c053b44f458b6ffb0d07a1db60.png][]

(2)在“指定模板参数的值”对话框中将Procedure\_Name参数对应的名称修改为Proc\_Stu,单击“确定”按钮，关闭此对话框。

(3)在创建存储过程的窗口中，将对应的SELECT语句修改为以下语句：

SELECT Sno,Sname,Sex,Sage
    FROM Student
    WHERE Sex = 'Male';

(4)然后进行分析并执行， 调用存过查看结果：

![f13e98f56f8e4b27a125f23ebc4dcae8.png][]

完整存储过程代码如下：

-- ================================================
    -- Template generated from Template Explorer using:
    -- Create Procedure (New Menu).SQL
    --
    -- Use the Specify Values for Template Parameters 
    -- command (Ctrl-Shift-M) to fill in the parameter 
    -- values below.
    --
    -- This block of comments will not be included in
    -- the definition of the procedure.
    -- ================================================
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    -- =============================================
    -- Author:		CL
    -- Create date: 2023/10/21
    -- Description:	查询学生表中男生的信息
    -- =============================================
    CREATE PROCEDURE Proc_stu 
    	-- Add the parameters for the stored procedure here
    	@p1 int = 0, 
    	@p2 int = 0
    AS
    BEGIN
    	-- SET NOCOUNT ON added to prevent extra result sets from
    	-- interfering with SELECT statements.
    	SET NOCOUNT ON;
    
        -- Insert statements for procedure here
    	SELECT Sno,Sname,Sex,Sage
        FROM Student
        WHERE Sex = 'Male';
    END
    GO

使用了之前提到的 `SET ANSI_NULLS ON` 和 `SET QUOTED_IDENTIFIER ON` 的设置，并且定义名为 `Proc_stu` 的存储过程。

存储过程 `Proc_stu` 接受两个参数 `@p1` 和 `@p2`，并设置它们的默认值为 0。在存储过程的主体部分，通过 `SELECT` 语句从 `Student` 表中选择学号（`Sno`）、姓名（`Sname`）、性别（`Sex`）和年龄（`Sage`）字段，并根据性别为男性进行筛选。

使用 `SET NOCOUNT ON` 可以防止额外的结果集干扰 `SELECT` 语句。

最后的 `GO` 命令用于将整个存储过程的定义分隔为一个单独的批处理。

通过执行 `EXEC Proc_stu` 来调用该存储过程，并获取满足条件的男生的学号、姓名、性别和年龄信息。

### 4.2使用CREATE PROC语句创建存储过程 ###

在SQL语言中，可以使用CREATE PROCEDURE语句创建存储过程，其语法格式如下：

CREATE PROCEDURE procedure_name
        [ @parameter1 datatype [ = default_value ] [ OUT | OUTPUT ]
          , ... ]
    AS
    BEGIN
        -- 在此处编写存储过程的主体代码
    END

关键字和符号的含义如下：

*  `CREATE PROCEDURE`：用于创建存储过程的关键字。
 *  `procedure_name`：指定存储过程的名称。遵循标识符的命名规则。
 *  `@parameter1`：可选项，指定存储过程的输入或输出参数名称。遵循标识符的命名规则。
 *  `datatype`：参数的数据类型。可以是任何有效的SQL Server数据类型。
 *  `default_value`：可选项，指定参数的默认值。
 *  `OUT` 或 `OUTPUT`：可选项，用于指定参数是输出参数。
 *  `AS`：开始定义存储过程的主体部分。
 *  `BEGIN` 和 `END`：用于定义存储过程的主体代码块。

【例1】使用CREATE PROCEDURE语句创建一个存储过程，用来根据学生编号查询学生信  
息。  
SQL语句如下：

Create Procedure Proc_Student
    @Proc_Sno int
    as
    select *from Student where Sno = @Proc_Sno

调用该存储过程：

DECLARE @StudentNo INT
    SET @StudentNo = 1 -- 设置要查询的学生编号
    
    EXEC Proc_Student @Proc_Sno = @StudentNo

结果如图所示：

![f7cfa21d3a1b438eaecfcb280a138b1a.png][]

【例2】创建一个名为GetstudentDetails的存储过程，接受一个输入参数@sno和一个输出参数@sname。存储过程的主体部分包含了查询学生信息的SQL语句，并将学生的姓名赋值给输出参数@sname ，具体语句如下：

CREATE PROCEDURE GetstudentDetails
        @sno INT,
        @sname NVARCHAR(50) OUTPUT
    AS
    BEGIN
        SELECT @sname = Sname
        FROM Student
        WHERE Sno = @sno
    END

定义了名为 `GetstudentDetails` 的存储过程，它接受一个输入参数 `@sno` 和一个输出参数 `@sname`。

在存储过程的主体部分，使用 `SELECT` 语句从 `Student` 表中选择学生的姓名（`Sname`），并将其赋值给输出参数 `@sname`。查询条件是学号等于输入参数 `@sno` 的记录。

这样，当调用存储过程时，可以传递一个学号作为输入参数，并且存储过程会返回对应学号的学生姓名赋值给输出参数 `@sname`。例如：

DECLARE @studentName NVARCHAR(50)
    
    EXEC GetstudentDetails 1, @sname = @studentName OUTPUT
    
    SELECT @studentName AS 'Student Name'

结果如图所示：

![e1fe2e3b7687403294694b660c1c27bf.png][]

## 五、管理存储过程 ##

存储过程创建完成后，用户可以通过SQL Server Management Studio工具对其进行管理。数据库中的存储过程都被保存在“数据库”/“数据库名称”/“可编程性”/“存储过程”路径下。下面是使用SQL Server Management Studio工具对存储过程进行查看代码、修改代码及名称、删除、执行等的管理。

### 5.1执行存储过程 ###

EXECUTE用来执行Transact-.SQL中的命令字符串、字符串或执行下列模块之一：系统存储过程、用户定义存储过程、标量值用户定义函数或扩展存储过程。  
EXECUTE的语法格式如下：

EXECUTE { procedure_name | @procedure_variable }
        [ { parameter_name = { expression | @variable [ OUTPUT ] } }
            [ ,...n ]
        ]

其中，关键字和符号的含义如下：

*  `EXECUTE`：用于执行存储过程或调用可执行的批处理。
 *  `procedure_name`：指定要执行的存储过程的名称。遵循标识符的命名规则。
 *  `@procedure_variable`：可选项，指定一个变量来保存存储过程的名称。
 *  `parameter_name`：可选项，指定存储过程的输入参数名称。遵循标识符的命名规则。
 *  `expression`：指定参数的值，可以是常量、变量或表达式。
 *  `@variable`：指定一个变量作为参数的值。
 *  `OUTPUT`：可选项，在参数名称后面加上 `OUTPUT` 关键字表示该参数是输出参数。

使用 `EXECUTE` 语句时，可以直接指定存储过程的名称并提供参数的值，也可以通过变量来保存存储过程的名称，并在执行时传递参数的值。

以下是一些示例说明了 `EXECUTE` 语句的使用：

-- 直接执行存储过程并传递参数值
    EXECUTE GetstudentDetails @sno = 1, @sname = @studentName OUTPUT
    
    -- 使用变量保存存储过程的名称，并传递参数值
    DECLARE @procName NVARCHAR(50)
    SET @procName = 'GetstudentDetails'
    
    EXECUTE @procName @sno = 1, @sname = @studentName OUTPUT

### 5.2查看存储过程 ###

许多系统存储过程、系统函数和目录视图都提供有关存储过程的信息。可以使用这些系统存储过程来查看存储过程的定义，即用于创建存储过程的Transact-.SQL语句。  
可以通过下面3种系统存储过程和目录视图查看存储过程。

#### 5.2.1.使用sys.sql\_modules查看存储过程的定义 ####

sys.sql\_modules为系统视图，通过该视图可以查看数据库中的存储过程，查看存储过程的操作方  
法如下：  
(1)单击工具栏中的新建查询按钮，新建查询编辑器。  
(2)在新建查询编辑器中输入如下代码：

select * from sys.sql_modules;

执行该语句即可查看到已创建的存储过程：

![faf873908975447584b6e492fe0b4502.png][]

#### 5.2.2.使用OBJECT\_DEFINITION查看存储过程的定义 ####

返回指定对象定义的Transact-.SQL源文本，语法格式如下：

OBJECT_DEFINITION (object_id)

参数说明：  
object\_id:要使用的对象的D。object\_id的数据类型为int,并假定表示当前数据库上下文中的对象。

【例】使用OBJECT\_DEFINITION查看ID为1285579618存储过程的代码,SQL语句如下：

select OBJECT_DEFINITION(1285579618)

结果如图所示：

![9e2350980a014b8aa2d8d2717c3be157.png][]

#### 5.2.3.使用sp\_helptext查看存储过程的定义 ####

显示用户定义规则的定义、默认值、未加密的Transact--SQL存储过程、用户定义Transact-SQL函  
数、触发器、计算列、CHECK约束、视图或系统对象（如系统存储过程）。语法格式如下：

sp_helptext @objname =]'name'[,@columnname computed_column_name

参数说明：

*  ☑\[@objname=\]name':架构范围内的用户定义对象的限定名称和非限定名称。仅当指定限定对象时才需要引号。如果提供的是完全限定名称（包括数据库名称），则数据库名称必须是当前数据库的名称。对象必须在当前数据库中。name的数据类型为nvarchar(776),无默认值。
 *  ☑\[@columnname=\]computed\_column\_name:要显示其定义信息的计算列的名称，必须将包含列的表指定为name。column\_name的数据类型为sysname,无默认值。

【例】通过sp\_helptext系统存储过程查看名为Proc\_Stu存储过程的代码。SQL语句如下：

sp_helptext 'Proc Stu'

结果如图所示：

![976fabbbfaa14c7daa4171dab1a5ba0c.png][]

> **提示：**
> 
> 上述的查看方法操作步骤如下：
> 
>  *  (1)打开SQL Server Management Studio,并连接到SQL Server2008中的数据库。
>  *  (2)选择存储过程所在的数据库，如db2008\_SJCX数据库。
>  *  (3)单击工具栏中的新建询按钮，新建查询编辑器，并输入SQL语句代码：
>  *  (4)单击【执行】按钮，就可以执行上述SQL语句代码。

### 5.3修改存储过程 ###

修改存储过程可以改变存储过程当中的参数或者语句，可以通过SQL语句中的**ALTER PROCEDURE**语句实现。虽然删除并重新创建该存储过程，也可以达到修改存储过程的目的，但是将丢失与该存储过程关联的所有权限。

#### 5.3.1.ALTER PROCEDURE语句 ####

ALTER PROCEDURE语句用来修改通过执行CREATE PROCEDURE语句创建的过程。该语句修改存储过程时不会更改权限，也不影响相关的存储过程或触发器。  
ALTER PROCEDURE语句的语法格式如下：

ALTER PROCEDURE procedure_name
    [ { @parameter1 data_type [ VARYING ] [= default] [ OUT | OUTPUT | READONLY ]
        [ ,...n ]
      | [ = { NULL | NOT NULL } ]
      }
    ]
    [ WITH RECOMPILE ]
    [,...n]

其中，关键字和符号的含义如下：

*  `ALTER PROCEDURE`：用于修改存储过程的定义。
 *  `procedure_name`：指定要修改的存储过程的名称。遵循标识符的命名规则。
 *  `@parameter1`：可选项，指定要修改的存储过程的参数名称。遵循标识符的命名规则。
 *  `data_type`：指定参数的数据类型。
 *  `VARYING`：可选项，指定参数是否是可变长度的。
 *  `default`：可选项，指定参数的默认值。
 *  `OUT`、`OUTPUT`、`READONLY`：可选项，用于指定参数的特性，如输出参数或只读参数。
 *  `= { NULL | NOT NULL }`：可选项，用于指定参数是否允许为空。
 *  `WITH RECOMPILE`：可选项，用于重新编译存储过程的执行计划。

【例1】`ALTER PROCEDURE` 语句的使用：

-- 修改存储过程的参数定义
    ALTER PROCEDURE GetstudentDetails
        @sno INT,
        @sname NVARCHAR(50)
    AS
    BEGIN
        -- 存储过程的逻辑代码
    END
    
    -- 强制重新编译存储过程
    ALTER PROCEDURE GetstudentDetails
    WITH RECOMPILE
    AS
    BEGIN
        -- 存储过程的逻辑代码
    END

第一个示例修改了存储过程的参数定义，可以增加、删除或修改存储过程的参数。第二个示例使用 `WITH RECOMPILE` 选项来强制重新编译存储过程的执行计划，以确保每次执行都使用最新的优化计划。

【例2】通过ALTER PROCEDURE语句修改名为Proc Stu的存储过程，具体操作步骤如下：  
(1)打开SQL Server Management Studio,并连接到SQL Server2008中的数据库。  
(2)选择存储过程所在的数据库，如db2008数据库。  
(3)单击工具栏中的新建查询按钮，新建查询编辑器，并输入如下SQL语句代码：

--修改存过sno参数类型：
    ALTER PROCEDURE [Proc_Stu]
    @Sno varchar(10)
    as
    select sage, sname from student where Sno = @Sno;

(4)单击【执行】按钮，就可以执行上述SQL语句代码，执行结果如图所示。

![cdef2722e11d43f9989fc158897aa4a7.png][]

除了上述方法修改存储过程外，也可以通过SQL Server20O8自动生成的ALTER PROCEDURE语  
句修改存储过程。以修改系统数据库master中系统存储过程sp\_MScleanupmergepublisher为例，操作步骤如下：  
(1)打开SQL Server Management Studio,并连接到SQL Server2008中的数据库。  
(2)展开“对象资源管理器”中“数据库/系统数据库/master/可编程性/系统存储过程”的节点后，在sp\_MScleanupmergepublisher系统存储过程上右击，弹出快捷菜单，如图所示。

![5b771f31806e4ad9923231210a82881e.png][]  
(3)选择“修改”命令，在查询编辑器中自动生成修改该存储过程的语句，生成的语句如图所示。

![633fc7b2e8504dc3b4131cbb6a62da79.png][]  
(4)修改该段SQL语句并执行，即可完成修改该存储过程。

### 5.4重命名存储过程 ###

重新命名存储过程可以通过手动操作或执行sp rename系统存储过程实现。

#### 5.4.1.手动操作重新命名存储过程 ####

(1)打开SQL Server Management Studio,.并连接到SQL Server2008中的数据库。  
(2)展开“对象资源管理器”中“数据库”/“数据库名称”/“可编程性”/“存储过程”节点，右击需要重新命名的存储过程，在弹出的快捷菜单中选择“重命名”命令。

例如，修改数据库中的Proc\_stu存储过程名称，如图所示。

![bf7b7f92cba24a5d94da2cf1f8561ec3.png][]

(3)此时，在存储过程名称的文本框中输入要修改的名称，即可重命名存储过程。

#### 5.4.2.执行sp\_rename系统存储过程重新命名存储过程 ####

sp\_rename系统存储过程可以在当前数据库中更改用户创建对象的名称。此对象可以是表、索引、  
列、别名数据类型或Microsoft.NET Framework公共语言运行时(CLR)用户定义类型。`sp_rename` 接受三个参数，分别是当前的对象名称、新的对象名称以及对象类型。语法格式如下：

EXEC sp_rename 'current_object_name', 'new_object_name', 'object_type'

其中，关键字和符号的含义如下：

*  `sp_rename`：用于重命名数据库对象的系统存储过程。
 *  `'current_object_name'`：指定要重命名的当前对象的名称。
 *  `'new_object_name'`：指定要将当前对象重命名为的新对象的名称。
 *  `'object_type'`：指定当前对象的类型。对于存储过程，请使用 `'PROCEDURE'`。

> **注意：**
> 
> 在执行 `sp_rename` 之前，应确保已经备份了数据库，并且谨慎操作以避免不可恢复的错误。

【例】将Proc\_Stu存储过程重新命名为Proc\_StuInfo。SQL语句如下：

EXEC sp_rename 'Proc_Stu', 'Proc_StuInfo';

结果检查如图所示：

![816229bae6dd4c8e816670ba3ea66c70.png][]

> **注意：**
> 
> 更改对象名的任一部分都可能破坏脚本和存储过程。建议不要使用此语句来重命名存储过程、触发器、用户定义函数或视图，而是删除该对象，然后使用新名称重新创建该对象。

### 5.5删除存储过程 ###

数据库中某些不再应用的存储过程可以将其删除，这样节约该存储过程所占的数据库空间。删除  
存储过程可以通过手动删除或执行DROP PROCEDURE语句实现。

#### 5.5.1手动删除存储过程 ####

(1)打开.SQL Server Management Studio,并连接到SQL Server2008中的数据库。  
(2)展开“对象资源管理器”中“数据库”/“数据库名称”/“可编程性”/“存储过程”节点，右击要删除的存储过程，在弹出的快捷菜单中选择“删除”命令。  
(3)在“删除对象”窗口中确认所删除的存储过程，单击“确定”按钮即可将该存储过程删除。如删除Proc\_Stulnfo存储过程，如图所示。

![c0ff381680fb41b7b893c8412d2dbbf4.png][]

#### 5.5.2.执行DROP PROCEDURE语句删除存储过程 ####

DROP PROCEDURE语句用来从当前数据库中删除一个或多个存储过程。语法格式如下：

DROP PROCEDURE [schema_name.] procedure_name;

其中，关键字和符号的含义如下：

*  `DROP PROCEDURE`：用于删除存储过程的语句。
 *  `[schema_name]`：可选项，指定存储过程所属的架构名称。如果省略，则假定为默认架构。
 *  `procedure_name`：指定要删除的存储过程的名称。

【例】删除名为Proc Student的存储过程。SQL语句如下：

DROP PROCEDURE Proc_Student

(3)单击【执行】按钮，就可以执行上述SQL语句代码将Proc\_Student存储过程删除。

> **提示：**
> 
> 不可以删除正在使用的存储过程，否则Microsoft SQL Server2008将在执行调用进程时显示一条错误消息。

## 六、存过创建与调试步骤 ##

在 SQL Server 2008 中，创建存储过程的流程步骤如下：

*  1. 打开 SQL Server Management Studio (SSMS)。
 *  2. 连接到适当的 SQL Server 数据库实例。
 *  3. 在 "查询" 窗口中输入 CREATE PROCEDURE 语句来定义存储过程。确保提供了存储过程的名称、参数、主体和逻辑等。

CREATE PROCEDURE procedure_name
       [ { @parameter1 data_type [ VARYING ] [= default] [ OUT | OUTPUT | READONLY ]
           [ ,...n ]
         | [ = { NULL | NOT NULL } ]
         }
       ]
       AS
       BEGIN
           -- 存储过程的逻辑代码
       END

*  4. 在 "查询" 窗口中执行 CREATE PROCEDURE 语句，以创建存储过程。

完成上述步骤后，存储过程将被创建并保存到数据库中。

调试和测试存储过程的过程可以通过以下步骤来进行：

*  1. 打开一个新的 "查询" 窗口。
 *  2. 使用 EXECUTE 或 EXEC 关键字，调用存储过程，并传入所需的参数。

EXECUTE procedure_name @parameter1 = value1, @parameter2 = value2;

*  3. 在 "查询" 窗口中执行调用存储过程的语句。
 *  4. 检查返回的结果或输出参数，以验证存储过程的行为是否符合预期。

如果存储过程中存在错误或需要更改其定义，可以使用 ALTER PROCEDURE 语句进行修改，并重新执行相应的步骤来更新存储过程。

> **注意**：
> 
> 为了进行有效的调试和测试，建议在存储过程中添加适当的错误处理和日志记录机制，以便在出现问题时能够跟踪和诊断问题。

【例1】 在 SQL Server 2008 中创建存储过程后，添加适当的错误处理和日志记录机制帮助跟踪和诊断问题。

CREATE PROCEDURE Test_Procedure
    AS
    BEGIN
        BEGIN TRY
            -- 存储过程的逻辑代码
    
            -- 例如，查询一个不存在的表
            SELECT * FROM NonexistentTable;
        END TRY
        BEGIN CATCH
            -- 记录错误信息到日志表
            INSERT INTO ErrorLog (ErrorMessage, ErrorDateTime)
            VALUES (ERROR_MESSAGE(), GETDATE());
            
            -- 抛出异常，可根据需要自定义处理方式
            THROW;
        END CATCH;
    END

在这个示例中，当查询一个不存在的表时会引发一个异常。使用 **`TRY...CATCH` 块**捕获异常，并在 `CATCH` 块中记录错误信息到日志表 `ErrorLog`。大家可以根据实际需求自定义错误处理方式，如发送电子邮件通知管理员、回滚事务等。

注意以下几点：

1.  可以根据需要创建一个名为 `ErrorLog` 的日志表，用于记录错误信息。表结构可以包含错误消息、日期时间戳等字段。
2.  使用 `ERROR_MESSAGE()` 函数获取捕获的异常的详细错误消息。
3.  使用 `GETDATE()` 函数获取当前日期时间戳，并将其插入到错误日志表中。
4.  使用 `THROW` 关键字抛出异常，以便在调试或测试过程中终止存储过程的执行。

通过将错误信息记录到日志表，您可以随时查看和分析存储过程执行过程中的错误。这样能够更容易地追踪问题并及时采取适当的措施。一定要根据实际需求和环境选择适合的错误处理和日志记录方法，并确保在生产环境中进行充分测试和验证。

为了便于理解，创建上述中的错误日志表：

CREATE TABLE ErrorLog (
        LogID INT IDENTITY(1,1) PRIMARY KEY,
        ErrorMessage NVARCHAR(MAX),
        ErrorDateTime DATETIME
    );

三个字段：

1.  `LogID`：作为错误日志的唯一标识符，使用 `IDENTITY(1,1)` 设置为自增。
2.  `ErrorMessage`：存储错误消息的字段，使用 `NVARCHAR(MAX)` 数据类型以容纳较大的错误消息。
3.  `ErrorDateTime`：存储错误发生的日期和时间戳的字段，使用 `DATETIME` 数据类型。

【例2】还是上面的存过，然后模拟发送电子邮件的过程：

USE msdb;
    GO
    
    CREATE PROCEDURE Test_Procedure
    AS
    BEGIN
        BEGIN TRY
            -- 存储过程的逻辑代码
    
            -- 例如，查询一个不存在的表
            SELECT * FROM NonexistentTable;
        END TRY
        BEGIN CATCH
            -- 记录错误信息到日志表
            INSERT INTO ErrorLog (ErrorMessage, ErrorDateTime)
            VALUES (ERROR_MESSAGE(), GETDATE());
            
            -- 发送电子邮件通知管理员（示例中仅打印消息）
            DECLARE @subject NVARCHAR(100) = 'Error in Test_Procedure';
            DECLARE @body NVARCHAR(MAX) = 'An error occurred in Test_Procedure. Please check the error log for more details.';
            PRINT 'Sending email: Subject - ' + @subject;
            PRINT 'Body - ' + @body;
            
            -- 抛出异常，可根据需要自定义处理方式
            THROW;
        END CATCH;
    END

在 SQL Server 2008 中，发送电子邮件需要使用额外的扩展功能，如 SQL Server Agent 或 CLR 集成。由于当前环境限制了这些功能，因此这里只是提供一个简单的示例模拟。该示例仅为概念演示，并不包含实际的电子邮件发送功能。

在这个示例中，当查询一个不存在的表时会引发一个异常。在 `CATCH` 块中，除了记录错误信息到日志表之外，还通过打印消息的方式模拟发送电子邮件通知管理员。实际上，大家可以替换 `PRINT` 语句为相应的电子邮件发送代码。

> **注意：**
> 
> 真正的电子邮件发送功能需要进一步的配置和集成，以便与 SQL Server 进行通信并发送电子邮件。如果需要实际的电子邮件通知，请考虑使用其他方法或工具来实现，如 SQL Server Agent 或基于 CLR 的扩展。

【例3】当查询一个不存在的表时引发异常时，回滚事务：

CREATE PROCEDURE Test_Procedure
    AS
    BEGIN
        BEGIN TRY
            BEGIN TRANSACTION;
            
            -- 存储过程的逻辑代码
    
            -- 例如，查询一个不存在的表
            SELECT * FROM NonexistentTable;
            
            COMMIT TRANSACTION;
        END TRY
        BEGIN CATCH
            ROLLBACK TRANSACTION;
            
            -- 记录错误信息到日志表
            INSERT INTO ErrorLog (ErrorMessage, ErrorDateTime)
            VALUES (ERROR_MESSAGE(), GETDATE());
            
            -- 抛出异常，可根据需要自定义处理方式
            THROW;
        END CATCH;
    END

使用 `BEGIN TRANSACTION` 开始一个新的事务，并在 `COMMIT TRANSACTION` 之前的逻辑代码执行完成后提交事务。如果在逻辑代码执行期间引发了异常，会进入 `CATCH` 块，其中使用 `ROLLBACK TRANSACTION` 回滚事务。

注意以下几点：

*  在 `TRY` 块内的逻辑代码应该包含要在同一事务中进行的操作。
 *  如果发生异常，将进入 `CATCH` 块，并通过 `ROLLBACK TRANSACTION` 来回滚未提交的事务。
 *  在回滚事务之前，可以根据需要记录错误信息到日志表。
 *  使用 `THROW` 关键字抛出异常，以便在调试或测试过程中终止存储过程的执行。

> **提示：**
> 
> 大家一定要谨慎使用事务，确保只在必要的情况下使用，并在实际环境中做好充分的测试和验证。

> ***本次关于SQL sever2008中存储过程的总结和整理就暂时结束了 ，如果文章内容存在错误的地方欢迎码友们的留言指导，或者有关存储过程的其他相关知识也可以留言告知，本木码人一定虚心接受各位的良言，成为一名优秀的猿人。***

[http_t.csdnimg.cn_Z8AnH]: http://t.csdnimg.cn/Z8AnH
[1c14d81cee7149e097b99d9a1f54c0c1.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/13/b6a6fd46416c4089a9eeaf9a1503c821.png
[41e518c56b854ef68cf1c39a13d52b6c.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/13/fb9df2c71f414b6487972cf315455f1e.png
[563787c053b44f458b6ffb0d07a1db60.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/13/99d664fec1954d969a468be8b9eba105.png
[f13e98f56f8e4b27a125f23ebc4dcae8.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/13/2a6324556cbc465cab8c45aa3bec1c88.png
[f7cfa21d3a1b438eaecfcb280a138b1a.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/13/bec620149f6241d3981508673ffdc7b6.png
[e1fe2e3b7687403294694b660c1c27bf.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/13/7a4b3d664f4948949ec510c486195ed3.png
[faf873908975447584b6e492fe0b4502.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/13/6cde060165af4ba5adaf55f2abf73bd2.png
[9e2350980a014b8aa2d8d2717c3be157.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/13/0e122ecf7afa4f52be80c3517a835c38.png
[976fabbbfaa14c7daa4171dab1a5ba0c.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/13/b21010a713934521b1cdbb7fa7ef619d.png
[cdef2722e11d43f9989fc158897aa4a7.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/13/7c68d2db9fb24c38a368f95efde7f053.png
[5b771f31806e4ad9923231210a82881e.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/13/650f99245cbb4dd78af065fea4044ca5.png
[633fc7b2e8504dc3b4131cbb6a62da79.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/13/08d84ae5478a470284bec9e035e2bbb2.png
[bf7b7f92cba24a5d94da2cf1f8561ec3.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/13/26525cb54e6a4bed8f383bc17f3be3f5.png
[816229bae6dd4c8e816670ba3ea66c70.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/13/cb9920cdfe704e01a6eae0c55611ea85.png
[c0ff381680fb41b7b893c8412d2dbbf4.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/13/1c57c1d83d8b47a0a384ea6d9ea49313.png