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

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 赋值的区别

场景	SET	SELECT
单值赋值	✅ 推荐	✅ 可用
多变量同时赋值	❌ 需要多条	✅ 一条语句
查询无返回值	变量不变（保持原值）	变量变为 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

特性	RAISERROR	THROW
引入版本	SQL Server 7.0	SQL 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 的安全性必须时刻牢记，生产环境禁止直接拼接用户输入。

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