SQL Server 开发系列（第二期）：T-SQL 编程基础——变量、流程控制与动态 SQL

绩隐金
0 阅读11分钟

SQL Server 开发系列（第二期）：T-SQL 编程基础——变量、流程控制与动态 SQL

上一期我们深入讲解了数据类型的选择。这一期，我们进入 T-SQL 编程的核心——变量、流程控制和动态 SQL。这些是编写存储过程、函数和脚本的基础。但你是否遇到过：WHILE 循环跑得比乌龟还慢？动态 SQL 导致注入漏洞？游标让服务器内存爆炸？这一期，我们不仅教你“怎么写”，更教你“怎么写才高效、安全”。

一、变量：声明、赋值与作用域

1.1 变量基础

-- 声明变量
DECLARE @Counter INT;
DECLARE @UserName NVARCHAR(50), @Age TINYINT;

-- 赋值方式1：SET（推荐，一次赋一个值）
SET @Counter = 1;
SET @UserName = '张三';

-- 赋值方式2：SELECT（可以从表查询赋值）
SELECT @Age = Age FROM Users WHERE UserID = 1;
-- 注意：如果查询返回多行，SELECT 赋值为最后一行，SET 会报错

-- 变量使用
PRINT @UserName;
SELECT @Counter AS CounterValue;

1.2 SET vs SELECT 赋值的区别

场景SETSELECT
单值赋值✅ 推荐✅ 可用
多变量同时赋值❌ 需要多条✅ 一条语句
查询无返回值变量不变（保持原值）变量变为 NULL
查询返回多行❌ 报错✅ 取最后一行
性能相同略快（批量操作）
-- SELECT 同时给多个变量赋值
SELECT 
    @UserName = UserName,
    @Age = Age
FROM Users 
WHERE UserID = 1;

-- ⚠️ 陷阱：查询无返回值时
DECLARE @Name VARCHAR(50) = 'Default';
SELECT @Name = Name FROM Users WHERE 1=0;  -- 无结果
PRINT @Name;  -- 输出 NULL（不是 'Default'！）

-- 安全写法：使用 SET 或先判断
IF EXISTS(SELECT 1 FROM Users WHERE UserID = 999)
    SELECT @Name = Name FROM Users WHERE UserID = 999;
ELSE
    SET @Name = 'Default';

1.3 变量的作用域

变量只在当前批处理（Batch）或存储过程内有效，以 GO 为分隔。

DECLARE @BatchVar INT = 1;
PRINT @BatchVar;  -- 输出 1

GO  -- 批处理结束

PRINT @BatchVar;  -- ❌ 错误：变量未声明

二、流程控制

2.1 IF...ELSE

-- 基础语法
IF condition
BEGIN
    -- 多条语句
END
ELSE IF condition2
BEGIN
    -- 语句
END
ELSE
BEGIN
    -- 语句
END

-- 实战示例：检查是否存在再操作
IF EXISTS(SELECT 1 FROM Users WHERE UserID = @UserID)
BEGIN
    UPDATE Users SET LastLogin = GETDATE() WHERE UserID = @UserID;
    PRINT '用户存在，已更新最后登录时间';
END
ELSE
BEGIN
    PRINT '用户不存在';
END

常见陷阱：

-- ❌ 错误：忘记 BEGIN...END，只有第一行属于 IF
IF @Value = 1
    SET @Result = 'One';
    SET @Flag = 1;  -- 这行总是执行！

-- ✅ 正确：使用 BEGIN...END
IF @Value = 1
BEGIN
    SET @Result = 'One';
    SET @Flag = 1;
END

2.2 WHILE 循环

-- 基础语法
WHILE condition
BEGIN
    -- 循环体
    
    IF some_condition
        BREAK;  -- 跳出循环
    
    IF another_condition
        CONTINUE;  -- 跳过本次循环剩余部分
END

实战：批量处理大量数据

-- ✅ 推荐：分批处理，避免长事务
DECLARE @BatchSize INT = 1000;
DECLARE @RowsAffected INT = 1;

WHILE @RowsAffected > 0
BEGIN
    DELETE TOP (@BatchSize) 
    FROM Logs
    WHERE LogDate < DATEADD(year, -1, GETDATE());
    
    SET @RowsAffected = @@ROWCOUNT;
    
    -- 批次间休息，减少锁竞争
    WAITFOR DELAY '00:00:01';
END

❌ 错误：WHILE 循环代替集合操作

-- ❌ 极差：逐行更新，100万行需要数小时
DECLARE @i INT = 1, @Max INT = 1000000;
WHILE @i <= @Max
BEGIN
    UPDATE Orders SET Status = 'Processed' WHERE OrderID = @i;
    SET @i = @i + 1;
END

-- ✅ 正确：一条 UPDATE 搞定
UPDATE Orders SET Status = 'Processed' 
WHERE OrderID BETWEEN 1 AND 1000000;
-- 或者使用分批 + 集合操作

2.3 CASE 表达式

CASE 不是语句，是表达式，可以在 SELECT 中使用。

-- 简单 CASE
SELECT 
    OrderID,
    CASE Status
        WHEN 1 THEN '待付款'
        WHEN 2 THEN '已付款'
        WHEN 3 THEN '已发货'
        ELSE '未知'
    END AS StatusName
FROM Orders;

-- 搜索 CASE
SELECT 
    Score,
    CASE 
        WHEN Score >= 90 THEN 'A'
        WHEN Score >= 80 THEN 'B'
        WHEN Score >= 70 THEN 'C'
        WHEN Score >= 60 THEN 'D'
        ELSE 'F'
    END AS Grade
FROM ExamResults;

-- ⚠️ CASE 是表达式，不能用于控制流程
-- ❌ 下面写法错误
CASE @Type
    WHEN 1 THEN UPDATE ...  -- 不能这样写
END

2.4 GOTO（尽量避免）

-- 很少使用，仅在错误处理跳转时有用
IF @ErrorCode <> 0
    GOTO ErrorHandler;

-- 正常流程
SELECT * FROM Users;
RETURN;

ErrorHandler:
    PRINT '发生错误';
    ROLLBACK;

三、动态 SQL

3.1 为什么需要动态 SQL？

-- 场景1：查询条件不确定
-- 用户可能传入 @ProductID、@CategoryID、@PriceMin 的任意组合

-- 场景2：表名/列名动态
-- 按月分表：Orders_202401, Orders_202402...

-- 场景3：动态 ORDER BY
-- 用户可以选择按不同列排序

3.2 EXEC vs sp_executesql

特性EXEC (@sql)sp_executesql
参数化❌ 不支持✅ 支持
计划缓存重用❌ 每次不同 SQL 都是新计划✅ 参数不同可重用计划
SQL 注入风险高（拼接字符串）低（使用参数）
性能差（频繁编译）好（计划重用）
复杂度简单稍复杂
-- ❌ 不推荐：EXEC 拼接
DECLARE @TableName VARCHAR(50) = 'Orders';
DECLARE @sql VARCHAR(MAX);
SET @sql = 'SELECT * FROM ' + @TableName + ' WHERE OrderID = ' + CAST(@OrderID AS VARCHAR);
EXEC(@sql);

-- ✅ 推荐：sp_executesql 参数化
DECLARE @sql NVARCHAR(MAX);
DECLARE @params NVARCHAR(MAX);
SET @sql = N'SELECT * FROM Orders WHERE OrderID = @OrderID';
SET @params = N'@OrderID INT';
EXEC sp_executesql @sql, @params, @OrderID = 100;

3.3 安全处理动态表名/列名

当表名/列名必须动态时，使用 QUOTENAME() 防止注入：

-- ✅ 安全：使用 QUOTENAME
DECLARE @TableName NVARCHAR(128) = 'Orders; DROP TABLE Users; --';
DECLARE @sql NVARCHAR(MAX);

-- QUOTENAME 会转义危险字符
SET @sql = N'SELECT * FROM ' + QUOTENAME(@TableName);
-- 实际生成：SELECT * FROM [Orders; DROP TABLE Users; --]
-- 作为表名会报错，但不会执行恶意代码

EXEC sp_executesql @sql;

3.4 动态搜索条件的正确写法

-- 场景：多条件组合查询（@ProductID, @CategoryID, @PriceMin 都可能是 NULL）

-- ✅ 方案1：使用参数化 + NULL 判断（推荐）
CREATE PROC SearchProducts
    @ProductID INT = NULL,
    @CategoryID INT = NULL,
    @PriceMin DECIMAL = NULL
AS
BEGIN
    SELECT * FROM Products
    WHERE (@ProductID IS NULL OR ProductID = @ProductID)
      AND (@CategoryID IS NULL OR CategoryID = @CategoryID)
      AND (@PriceMin IS NULL OR Price >= @PriceMin);
END
-- 优点：安全、计划重用
-- 缺点：可能存在参数嗅探问题

-- ✅ 方案2：动态 SQL + sp_executesql（复杂条件时）
CREATE PROC SearchProducts_Dynamic
    @ProductID INT = NULL,
    @CategoryID INT = NULL,
    @PriceMin DECIMAL = NULL
AS
BEGIN
    DECLARE @sql NVARCHAR(MAX) = N'SELECT * FROM Products WHERE 1=1';
    DECLARE @params NVARCHAR(MAX) = N'@ProductID INT, @CategoryID INT, @PriceMin DECIMAL';
    
    IF @ProductID IS NOT NULL
        SET @sql += N' AND ProductID = @ProductID';
    IF @CategoryID IS NOT NULL
        SET @sql += N' AND CategoryID = @CategoryID';
    IF @PriceMin IS NOT NULL
        SET @sql += N' AND Price >= @PriceMin';
    
    EXEC sp_executesql @sql, @params, 
        @ProductID, @CategoryID, @PriceMin;
END

3.5 SQL 注入防护清单

-- ❌ 绝对禁止：直接拼接用户输入
SET @sql = 'SELECT * FROM Users WHERE Name = ''' + @UserName + '''';
-- 输入：' OR '1'='1  → 查询所有用户

-- ✅ 必须做到：
-- 1. 使用 sp_executesql + 参数
-- 2. 对动态表名/列名使用 QUOTENAME()
-- 3. 对用户输入进行白名单验证（如排序字段）

-- 排序字段白名单验证
CREATE PROC GetProducts
    @SortColumn NVARCHAR(50) = 'ProductID',
    @SortDirection NVARCHAR(4) = 'ASC'
AS
BEGIN
    -- 白名单验证
    IF @SortColumn NOT IN ('ProductID', 'ProductName', 'Price', 'CreateDate')
        SET @SortColumn = 'ProductID';
    
    IF @SortDirection NOT IN ('ASC', 'DESC')
        SET @SortDirection = 'ASC';
    
    DECLARE @sql NVARCHAR(MAX) = N'SELECT * FROM Products ORDER BY ' 
        + QUOTENAME(@SortColumn) + N' ' + @SortDirection;
    
    EXEC sp_executesql @sql;
END

四、游标（尽量远离！）

4.1 为什么游标是性能杀手？

  • 逐行处理，每条数据都产生一次上下文切换
  • 锁持有时间长
  • 事务可能被拉长
  • 99% 的场景都可以用集合操作替代

4.2 游标的替代方案

-- ❌ 坏：游标逐行更新
DECLARE @OrderID INT;
DECLARE cur CURSOR FOR SELECT OrderID FROM Orders WHERE Status = 'Pending';
OPEN cur;
FETCH NEXT FROM cur INTO @OrderID;
WHILE @@FETCH_STATUS = 0
BEGIN
    UPDATE Orders SET ProcessDate = GETDATE() WHERE OrderID = @OrderID;
    FETCH NEXT FROM cur INTO @OrderID;
END
CLOSE cur;
DEALLOCATE cur;

-- ✅ 好：一条 UPDATE 搞定
UPDATE Orders SET ProcessDate = GETDATE() WHERE Status = 'Pending';

-- ✅ 如果必须分批，使用 WHILE + TOP
DECLARE @BatchSize INT = 1000;
WHILE EXISTS(SELECT 1 FROM Orders WHERE Status = 'Pending')
BEGIN
    UPDATE TOP (@BatchSize) Orders 
    SET ProcessDate = GETDATE() 
    WHERE Status = 'Pending';
    
    WAITFOR DELAY '00:00:01';
END

4.3 少数必须使用游标的场景

以下场景可以考虑使用游标（但仍需谨慎）：

  1. 复杂聚合计算：如移动平均、累计和（但窗口函数可替代）
  2. 调用存储过程：对每条记录执行一个存储过程
  3. 数据迁移中的特殊转换：逻辑太复杂无法用 SQL 表达
-- 当必须使用游标时，使用 FAST_FORWARD 和 READ_ONLY 提升性能
DECLARE cur CURSOR FAST_FORWARD READ_ONLY FOR
    SELECT OrderID, Amount FROM Orders;

五、错误处理：TRY...CATCH

5.1 基础用法

BEGIN TRY
    -- 可能出错的代码
    INSERT INTO Users (UserName) VALUES ('张三');
    
    -- 故意制造错误
    SELECT 1/0;
END TRY
BEGIN CATCH
    -- 错误处理
    PRINT '错误号: ' + CAST(ERROR_NUMBER() AS VARCHAR);
    PRINT '错误严重性: ' + CAST(ERROR_SEVERITY() AS VARCHAR);
    PRINT '错误状态: ' + CAST(ERROR_STATE() AS VARCHAR);
    PRINT '错误行号: ' + CAST(ERROR_LINE() AS VARCHAR);
    PRINT '错误消息: ' + ERROR_MESSAGE();
    
    -- 回滚事务（如果事务存在）
    IF @@TRANCOUNT > 0
        ROLLBACK;
END CATCH

5.2 事务中的错误处理

CREATE PROC TransferMoney
    @FromAccount INT,
    @ToAccount INT,
    @Amount DECIMAL(18,2)
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRY
        BEGIN TRANSACTION;
        
        -- 扣款
        UPDATE Accounts SET Balance = Balance - @Amount 
        WHERE AccountID = @FromAccount AND Balance >= @Amount;
        
        IF @@ROWCOUNT = 0
            THROW 50001, '余额不足', 1;
        
        -- 加款
        UPDATE Accounts SET Balance = Balance + @Amount 
        WHERE AccountID = @ToAccount;
        
        COMMIT TRANSACTION;
        PRINT '转账成功';
    END TRY
    BEGIN CATCH
        -- 回滚事务
        IF @@TRANCOUNT > 0
            ROLLBACK TRANSACTION;
        
        -- 重新抛出错误（让调用方处理）
        THROW;
    END CATCH
END

5.3 RAISERROR vs THROW

特性RAISERRORTHROW
引入版本SQL Server 7.0SQL Server 2012
严重级别可自定义固定为 16
错误号50000 以上50000 以上
参数化消息支持需要拼接
推荐度旧代码兼容✅ 推荐
-- RAISERROR（旧风格）
RAISERROR('余额不足，当前余额: %d', 16, 1, @Balance);

-- THROW（新风格）
THROW 50001, '余额不足', 1;

-- 使用 FORMATMESSAGE 支持参数化
DECLARE @Msg NVARCHAR(4000) = FORMATMESSAGE('余额不足，当前余额: %d', @Balance);
THROW 50001, @Msg, 1;

六、实战：完整的存储过程模板

CREATE PROC [dbo].[usp_UpdateOrderStatus]
    @OrderID INT,
    @NewStatus TINYINT,
    @UpdateUser NVARCHAR(50)
AS
BEGIN
    SET NOCOUNT ON;  -- 减少网络流量
    
    -- 变量声明
    DECLARE @OldStatus TINYINT;
    DECLARE @ErrorMsg NVARCHAR(4000);
    
    -- 参数验证
    IF @OrderID IS NULL OR @NewStatus IS NULL
    BEGIN
        THROW 50001, '参数不能为空', 1;
    END
    
    IF @NewStatus NOT IN (1, 2, 3, 4)
    BEGIN
        THROW 50002, '无效的状态值', 1;
    END
    
    BEGIN TRY
        BEGIN TRANSACTION;
        
        -- 获取当前状态（加行锁防止并发）
        SELECT @OldStatus = Status 
        FROM Orders WITH (UPDLOCK, ROWLOCK)
        WHERE OrderID = @OrderID;
        
        IF @OldStatus IS NULL
        BEGIN
            THROW 50003, '订单不存在', 1;
        END
        
        -- 状态流转验证
        IF (@OldStatus = 4 AND @NewStatus <> 4)
        BEGIN
            THROW 50004, '已完成订单不能修改状态', 1;
        END
        
        -- 更新
        UPDATE Orders 
        SET Status = @NewStatus,
            UpdateTime = GETDATE(),
            UpdateUser = @UpdateUser
        WHERE OrderID = @OrderID;
        
        -- 记录日志
        INSERT INTO OrderStatusLog (OrderID, OldStatus, NewStatus, UpdateUser)
        VALUES (@OrderID, @OldStatus, @NewStatus, @UpdateUser);
        
        COMMIT TRANSACTION;
        
        -- 返回成功
        SELECT 'SUCCESS' AS Result, '状态更新成功' AS Message;
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0
            ROLLBACK;
        
        SET @ErrorMsg = FORMATMESSAGE('更新订单状态失败，错误号: %d，消息: %s', 
            ERROR_NUMBER(), ERROR_MESSAGE());
        
        -- 记录错误日志
        INSERT INTO ErrorLog (ProcedureName, ErrorMsg, ErrorLine, ErrorDate)
        VALUES ('usp_UpdateOrderStatus', @ErrorMsg, ERROR_LINE(), GETDATE());
        
        -- 重新抛出
        THROW;
    END CATCH
END

七、性能优化要点

问题解决方案示例
游标用集合操作或 WHILE + TOP见 4.2
动态 SQL 无参数化使用 sp_executesql见 3.2
循环内重复查询临时表 + 批量操作将循环内的查询移到外面
长事务分批提交每 N 行 COMMIT
隐式转换保持类型一致WHERE ID = @ID（INT 对 INT）
大量 PRINT减少或禁用生产环境注释 PRINT

八、核心总结

知识点核心要点
变量SET 适合单值，SELECT 适合多值但注意 NULL 陷阱
IF/WHILE记住 BEGIN...END，WHILE 不能代替集合操作
动态 SQL优先 sp_executesql + 参数化，永远不要直接拼接
SQL 注入使用参数、QUOTENAME()、白名单验证
游标99% 场景不需要，用集合操作代替
错误处理TRY...CATCH + TRANSACTION + THROW
性能集合操作 > WHILE > 游标

一句话记住本期内容：

T-SQL 编程的核心是“以集合思维代替循环思维”——能一条 UPDATE 解决绝不写 WHILE，能用 sp_executesql 参数化绝不拼接字符串，能不用游标坚决不用。

动手练习

-- 问题1：以下存储过程有什么问题？如何优化？
CREATE PROC ProcessOrders
AS
BEGIN
    DECLARE @OrderID INT, @Total DECIMAL;
    DECLARE cur CURSOR FOR SELECT OrderID FROM Orders;
    OPEN cur;
    FETCH NEXT FROM cur INTO @OrderID;
    WHILE @@FETCH_STATUS = 0
    BEGIN
        SELECT @Total = SUM(Amount) FROM OrderDetails WHERE OrderID = @OrderID;
        UPDATE Orders SET TotalAmount = @Total WHERE OrderID = @OrderID;
        FETCH NEXT FROM cur INTO @OrderID;
    END
    CLOSE cur;
    DEALLOCATE cur;
END

-- 问题2：以下动态 SQL 有什么安全隐患？
CREATE PROC SearchUsers @Keyword VARCHAR(100)
AS
BEGIN
    DECLARE @sql VARCHAR(MAX) = 
        'SELECT * FROM Users WHERE UserName LIKE ''%' + @Keyword + '%''';
    EXEC(@sql);
END

-- 问题3：如何捕获并记录这个错误？
BEGIN TRY
    INSERT INTO Users (UserID, UserName) VALUES (1, '张三');  -- 假设 UserID=1 已存在
END TRY
BEGIN CATCH
    -- 这里应该怎么写？
END CATCH
点击查看参考答案

问题1 优化：

-- 一条 UPDATE + JOIN 搞定
UPDATE Orders 
SET TotalAmount = (
    SELECT SUM(Amount) 
    FROM OrderDetails 
    WHERE OrderDetails.OrderID = Orders.OrderID
);
-- 或使用 UPDATE FROM
UPDATE o
SET o.TotalAmount = ISNULL(SUM(d.Amount), 0)
FROM Orders o
LEFT JOIN OrderDetails d ON o.OrderID = d.OrderID
GROUP BY o.OrderID;

问题2 安全隐患：SQL 注入漏洞

-- 修复方案
CREATE PROC SearchUsers @Keyword VARCHAR(100)
AS
BEGIN
    DECLARE @sql NVARCHAR(MAX) = 
        N'SELECT * FROM Users WHERE UserName LIKE @Keyword';
    DECLARE @param NVARCHAR(MAX) = N'@Keyword VARCHAR(100)';
    SET @Keyword = '%' + @Keyword + '%';
    EXEC sp_executesql @sql, @param, @Keyword;
END

问题3 错误处理：

BEGIN CATCH
    INSERT INTO ErrorLog (ErrorNumber, ErrorMessage, ErrorDate)
    VALUES (ERROR_NUMBER(), ERROR_MESSAGE(), GETDATE());
    
    -- 如果需要重新抛出
    THROW;
END CATCH

下一期预告

DML 语句进阶——INSERT、UPDATE、DELETE、MERGE 的深度优化

  • 批量插入的 4 种方式与性能对比（逐行、BULK INSERT、TABLOCK、VALUES 多行）
  • UPDATE 的隐藏陷阱：为什么更新后索引会变慢？
  • 大表删除：分批删除 vs TRUNCATE vs 分区切换
  • MERGE：一条语句搞定插入/更新/删除（以及它的坑）
  • OUTPUT 子句：返回被影响的数据

📌 本文代码已在 SQL Server 2019+ 验证。动态 SQL 的安全性必须时刻牢记，生产环境禁止直接拼接用户输入。

本系列持续更新中，点击关注不错过第三期。

avatar
文章
阅读
粉丝