SQL Server 存储过程基础：创建、参数传值与游标应用实例

存储过程的基本结构与语法

在 SQL Server 中，存储过程是一段预编译的 SQL 代码集合，可以提高执行效率并增强安全性。虽然旧版本（如 SQL Server 2005）支持简化的书写方式，但为了保持代码的规范性和可维护性，现代开发建议遵循标准的 CREATE PROCEDURE 语法，并注意关键字的正确拼写。

一个标准的简单存储过程通常包含以下部分：

• 头部定义：使用 CREATE PROCEDURE 关键字，指定过程名称及参数列表。参数需明确类型，若需返回值必须显式声明 OUTPUT 关键字。
• 变量声明：使用 DECLARE 语句定义过程内部使用的局部变量。
• 业务逻辑：包含条件判断（IF...ELSE）、循环或数据操作语句，包裹在 BEGIN...END 块中。

CREATE PROCEDURE my_pro 
    @inputDate varchar(50),       -- 声明输入变量
    @Result varchar(255) OUTPUT   -- 声明输出变量，注意 OUTPUT 关键字
AS
BEGIN
    -- 声明局部变量
    DECLARE @variable1 varchar(255);
    DECLARE @variable2 int;

    -- 示例逻辑结构
    IF @variable2 > 100
    BEGIN
        SET @variable1 = 'Large Value';
    END
    ELSE
    BEGIN
        SET @variable1 = 'Small Value';
    END

    -- 设置输出值
    SET @Result = @variable1;
END
GO

删除与调用存储过程的方法

管理存储过程的生命周期主要涉及删除与调用两个动作。调用时需注意参数类型的匹配以及 OUTPUT 变量的传递方式。

第 1 步：删除存储过程

如果不再需要某个存储过程，可以使用 DROP PROCEDURE 命令将其从数据库中移除。这通常在测试重构或清理环境时使用。

DROP PROCEDURE my_pro;

第 2 步：执行无参数存储过程

对于不接受任何参数的存储过程，直接使用 EXECUTE 或 EXEC 后跟过程名称即可。

EXECUTE my_pro;

第 3 步：执行带输入参数的存储过程

传入具体的数值或字符串以触发特定的业务逻辑。参数按位置顺序传入。

EXECUTE my_pro '2023-10-01';

第 4 步：执行带输出参数的存储过程

这是最常用的场景之一。当需要从存储过程中获取计算结果时，必须先声明一个临时变量来接收返回值，并在调用时加上 OUTPUT 关键字标识该变量。

-- 1. 声明用于接收输出的变量
DECLARE @ResultValue varchar(1024);

-- 2. 执行存储过程，将返回值赋给 @ResultValue
EXECUTE my_pro '2023-10-01', @ResultValue OUTPUT;

-- 3. 查看执行结果
SELECT @ResultValue AS result;

实战案例：使用游标合并多行数据

在实际业务中，经常需要将某张表中符合条件的多行记录的某一列字段，拼接成一条以逗号分隔的长字符串返回。由于标准聚合函数（如 GROUP_CONCAT 或其他数据库的类似函数）在 SQL Server 早期版本中的限制或使用不便，使用游标（Cursor）是一个经典且稳妥的解决方案。

需求说明

假设有一张 Test 表，包含字段 Test01（数值型主键或ID）和 Test02（字符型数据）。我们需要找到所有 Test01 大于指定阈值 @id 的记录，并将这些记录对应的 Test02 值合并成一行，中间用逗号隔开。

示例数据：

实现逻辑分析

1. **创建/检查**：先判断存储过程是否存在，若存在则删除，确保可以重新创建。
2. **定义游标**：DECLARE ... CURSOR FOR SELECT ... 创建一个只进游标，仅选取需要的列。
3. **初始化**：清空输出变量 @Result。
4. **打开与遍历**：
- 打开游标。
- 使用 FETCH NEXT 将第一行数据读入局部变量。
- 使用 WHILE @@FETCH_STATUS = 0 判断是否还有下一行。
- 在循环内，若 @Result 为空，直接赋值；否则追加逗号和数据。
- 读取下一行，直到结束。
5. **释放资源**：关闭并释放游标，防止内存泄漏。

完整代码实现

-- 如果已存在则删除
IF OBJECT_ID('my_Cursor') IS NOT NULL
    DROP PROCEDURE my_Cursor;
GO

CREATE PROCEDURE my_Cursor 
    @id int,
    @Result varchar(8000) OUTPUT  -- 增加长度以防数据较多
AS
BEGIN
    DECLARE @Field int;
    DECLARE city_cursor CURSOR FOR 
        SELECT Test02 FROM Test WHERE Test01 > @id;
    
    -- 初始化输出变量
    SET @Result = '';
    
    OPEN city_cursor;
    FETCH NEXT FROM city_cursor INTO @Field;
    
    WHILE @@FETCH_STATUS = 0
    BEGIN
        -- 拼接逻辑：第一次不加逗号，后续加逗号
        IF @Result = '' 
        BEGIN
            SET @Result = @Field;
        END
        ELSE
        BEGIN
            SET @Result = @Result + ',' + @Field;
        END
        
        -- 读取下一条
        FETCH NEXT FROM city_cursor INTO @Field;
    END
    
    CLOSE city_cursor;
    DEALLOCATE city_cursor;
END
GO

-- 测试执行
DECLARE @FinalResult varchar(8000);
EXECUTE my_Cursor 10000, @FinalResult OUTPUT;
SELECT @FinalResult AS MergedResult;

上述代码执行后，@FinalResult 将返回类似 QAZ,WSX,EDC,RFV 的结果，完美实现了多行转单行的需求。

关键注意事项

• 参数声明：在调用存储过程时，声明接收输出值的变量必须在 DECLARE 中定义，且在 EXEC 调用时必须显式写出 OUTPUT，否则系统可能将其视为默认输入参数或报错。
• 游标性能：游标逐行处理数据，在大表情况下可能影响性能。如果数据量巨大，应优先考虑使用 SQL Server 2017+ 支持的 STRING_AGG() 函数或 FOR JSON PATH 等批量处理方式替代游标。
• 变量作用域：存储过程中的局部变量仅在过程内有效，外部需要通过输出参数才能获取其最终状态。

以上就是关于 SQL Server 存储过程创建、参数交互及游标应用的详细内容，更多关于 T-SQL 进阶优化的资料请关注本站其它相关文章！