方舟 · Ark

Appearance

T-SQL 常用业务处理代码模板 ​

💡 说明

本库旨在沉淀开发过程中高频使用的 T-SQL 代码片段与业务逻辑模板，通过标准化的写法提升代码的稳健性与开发效率。

1. 事务处理模板 (Transaction Handling) ​

在复杂的业务操作中，正确的事务嵌套与保存点处理是防止数据不一致的关键。

1.1 嵌套事务与保存点方案 ​

适用于需要在存储过程中处理可能存在的外部事务嵌套场景，通过 @@TRANCOUNT 判断是否需要创建 SAVE TRANSACTION。

sql
CREATE PROCEDURE [dbo].[Admin_GoodsStock_Box_UseBox]
    @orderid INT,
    @sn NVARCHAR(100)
AS
BEGIN
    SET NOCOUNT ON;

    -- 声明变量，记录当前已开启的事务数
    DECLARE @exist_trancount INT = @@TRANCOUNT;

    IF @exist_trancount > 0
        SAVE TRANSACTION tran_proc; -- 创建事务保存点，不增加 @@TRANCOUNT
    ELSE
        BEGIN TRANSACTION tran_proc; -- 开启新事务

    BEGIN TRY
        /*
        ============================================================
        业务处理逻辑开始
        ============================================================
        */

        -- 执行核心业务 SQL...

        SELECT 0 AS code, '成功' AS msg;

        /*
        ============================================================
        业务处理逻辑结束
        ============================================================
        */

        -- 若没有外部嵌套事务，则执行最终提交
        IF @exist_trancount = 0
            COMMIT TRANSACTION tran_proc;

        RETURN 1;
    END TRY
    BEGIN CATCH
        -- 出错处理：回滚至保存点或完全回滚
        IF @exist_trancount > 0
            ROLLBACK TRANSACTION tran_proc;
        ELSE
            ROLLBACK TRANSACTION;

        -- 记录错误日志（建议封装通用错误日志存储过程）
        -- EXECUTE dbo.uspLogError;

        RETURN -1;
    END CATCH
END;

1.2 标准 TRY...CATCH 事务方案 (含行数校验) ​

适用于独立的业务操作，强调通过 @@ROWCOUNT 校验更新结果，并结合 TRY...CATCH 进行异常处理。

sql
CREATE PROCEDURE HumanResources.uspEmployeeHireInfo2
    @BusinessEntityID INT,
    @JobTitle NVARCHAR(50),
    @HireDate DATETIME,
    @RateChangeDate DATETIME,
    @Rate MONEY,
    @PayFrequency TINYINT,
    @CurrentFlag BIT
AS
BEGIN
    SET NOCOUNT ON;

    BEGIN TRY
        BEGIN TRANSACTION;

        -- 1. 执行关联更新
        UPDATE HumanResources.Employee
        SET JobTitle = @JobTitle,
            HireDate = @HireDate,
            CurrentFlag = @CurrentFlag
        WHERE BusinessEntityID = @BusinessEntityID;

        -- 2. 检查更新是否受影响
        IF @@ROWCOUNT > 0
            BEGIN
                -- 3. 只有更新成功才执行后续插入
                INSERT INTO HumanResources.EmployeePayHistory (BusinessEntityID, RateChangeDate, Rate, PayFrequency)
                VALUES (@BusinessEntityID, @RateChangeDate, @Rate, @PayFrequency);
            END
        ELSE
            BEGIN
                -- 更新未命中，手动回滚并返回特定错误码
                PRINT 'BusinessEntityID Not Found';
                ROLLBACK TRANSACTION;
                RETURN -1000;
            END;

        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        -- 捕获异常并回滚
        IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;

        -- 记录错误日志
        -- EXECUTE dbo.uspLogError;

        -- 处理特定错误码（如主键冲突）
        IF ERROR_NUMBER() = 2627
            RETURN -2000;

        THROW; -- 重新抛出异常
    END CATCH
END;

2. 数据查询与分页 (Query & Pagination) ​

2.1 高性能分页：临时表 ID 过滤方案 ​

适用于数据量巨大且包含复杂 Join 的场景。核心思路是：先按排序规则将主键 ID 存入带自增序列的临时表，再关联主表取详细字段。

sql
-- 1. 创建临时索引表
CREATE TABLE #PageIndex
(
    [IndexId] INT IDENTITY(1, 1) PRIMARY KEY,
    [ProductId] INT NOT NULL
);

-- 2. 仅向临时表插入 ID（减少 IO 消耗，提升排序速度）
INSERT INTO #PageIndex ([ProductId])
SELECT ProductId
FROM #DisplayOrderTmp
GROUP BY ProductId
ORDER BY MIN([Id]);

-- 3. 获取总条数
DECLARE @TotalRecords INT = @@ROWCOUNT;

-- 4. 根据页码范围返回详细数据
SELECT TOP (@RowsToReturn) p.*
FROM #PageIndex [pi]
INNER JOIN Product p WITH (NOLOCK) ON p.Id = [pi].[ProductId]
WHERE [pi].IndexId > @PageLowerBound
  AND [pi].IndexId <= @PageUpperBound
ORDER BY [pi].IndexId;

DROP TABLE #PageIndex;

2.2 通用分页：主键排除法 (NOT IN) ​

适用于表结构相对简单、偏移量较小的分页。

sql
SET @SQL = 'SELECT TOP ' + CONVERT(VARCHAR(20), @pageSize) + ' * FROM ' + @tableName
         + ' WHERE ' + @condition + ' AND ' + @key + ' NOT IN ('
         + 'SELECT TOP ' + CONVERT(VARCHAR(20), @startIndex) + ' ' + @key
         + ' FROM ' + @tableName + ' WHERE ' + @condition + ');'

2.3 现代分页：ROW_NUMBER() 方案 ​

sql
SELECT * FROM (
    SELECT *, ROW_NUMBER() OVER(ORDER BY UserId ASC) AS RowNum
    FROM UserInfo
) AS T
WHERE T.RowNum BETWEEN ((@PageIndex - 1) * @PageSize + 1) AND (@PageSize * @PageIndex);

3. 常用业务操作技巧 ​

3.1 主从表插入 (SCOPE_IDENTITY) ​

sql
INSERT INTO Orders (OrderDate) VALUES (GETDATE());
-- 获取当前会话最近生成的标识值
DECLARE @NewOrderID INT = SCOPE_IDENTITY();
INSERT INTO OrderDetails (OrderID, ProductID) VALUES (@NewOrderID, 101);

3.2 字符串分割函数 (Split) ​

用于在 WHERE IN 子句中处理逗号分隔的字符串。

sql
CREATE FUNCTION [dbo].[Split] (@c VARCHAR(MAX), @split VARCHAR(50))
RETURNS @t TABLE ( col VARCHAR(50) )
AS
BEGIN
    WHILE ( CHARINDEX(@split, @c) <> 0 )
    BEGIN
        INSERT @t(col) VALUES ( SUBSTRING(@c, 1, CHARINDEX(@split, @c) - 1) )
        SET @c = STUFF(@c, 1, CHARINDEX(@split, @c), '')
    END
    INSERT @t(col) VALUES (@c)
    RETURN
END

3.3 多行合并拼接 (FOR XML PATH) ​

将多行记录的某个字段拼接成一个逗号分隔的字符串。

sql
SELECT STUFF(
    (SELECT ',' + Name
     FROM Users
     FOR XML PATH('')),
    1, 1, ''
) AS UserList;

3.4 关联 DML 操作 (Update/Delete Join) ​

  • 关联更新：

    sql
    UPDATE t1 SET t1.Col = t2.Col
FROM Table1 t1
INNER JOIN Table2 t2 ON t1.ID = t2.ID;

  • 关联删除：

    sql
    DELETE t1
FROM Table1 t1
INNER JOIN Table2 t2 ON t1.ID = t2.ID
WHERE t2.Status = 'Expired';

4. 架构与进阶模板 ​

4.1 触发器 (Trigger) 模板 ​

用于审计或跨表同步。

sql
CREATE TRIGGER trg_UpdateStock ON OrderDetails AFTER INSERT
AS
BEGIN
    UPDATE Products SET Stock = Stock - i.Quantity
    FROM Products p INNER JOIN inserted i ON p.Id = i.ProductId;
END

4.2 Group By 性能优化 ​

针对字段过多的聚合查询，建议先在子查询/CTE 中完成 GROUP BY 缩小范围，再与主表关联。

✅ 维护计划

  • [ ] 增加常用系统性能诊断查询脚本。
  • [ ] 沉淀 SQL Server 2012+ OFFSET...FETCH 的标准写法。

方舟 · Ark

Copyright © 2026-present WhatHappenin