时间戳

首页 > 解决方案 > 如何在 SQL Server 存储过程中安排重复代码？

sql - 如何在 SQL Server 存储过程中安排重复代码？

问题描述

我有以下存储过程：

IF EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'ChangeClientId') AND type in (N'P', N'PC'))
    DROP PROCEDURE ChangeClientId
GO

CREATE PROCEDURE [dbo].[ChangeClientId] 
    (@dst SYSNAME,
     @NewClientId INT,
     @dryRun BIT = 1)
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;

    DECLARE @TableOfSqls TABLE (Id INT IDENTITY PRIMARY KEY, Part INT, Sql NVARCHAR(MAX))
    DECLARE @Phase INT = 0
    ...

    INSERT INTO @TableOfSqls VALUES (0, 'TRUNCATE TABLE #Failure')
    INSERT INTO @TableOfSqls VALUES (0, 'GO')

    SET @Phase = @Phase + 1

    INSERT INTO @TableOfSqls VALUES (@Phase, '')
    INSERT INTO @TableOfSqls VALUES (@Phase, 'USE [' + @dst + ']')
    INSERT INTO @TableOfSqls VALUES (@Phase, 'SET XACT_ABORT ON')
    INSERT INTO @TableOfSqls VALUES (0, 'IF EXISTS (SELECT 1 FROM #Failure) THROW 50000, ''Skipped due to previous errors'', 0')
    INSERT INTO @TableOfSqls VALUES (0, 'INSERT INTO #Failure VALUES (0)')

    ...
END

这个代码块

    INSERT INTO @TableOfSqls VALUES (0, 'TRUNCATE TABLE #Failure')
    INSERT INTO @TableOfSqls VALUES (0, 'GO')

    SET @Phase = @Phase + 1

    INSERT INTO @TableOfSqls VALUES (@Phase, '')
    INSERT INTO @TableOfSqls VALUES (@Phase, 'USE [' + @dst + ']')
    INSERT INTO @TableOfSqls VALUES (@Phase, 'SET XACT_ABORT ON')
    INSERT INTO @TableOfSqls VALUES (0, 'IF EXISTS (SELECT 1 FROM #Failure) THROW 50000, ''Skipped due to previous errors'', 0')
    INSERT INTO @TableOfSqls VALUES (0, 'INSERT INTO #Failure VALUES (0)')

在存储过程中重复多次。在 C# 中，我会将它放在嵌套函数中。在 T-SQL 中，我应该能够创建一个临时存储过程或类似的东西，但我不确定。

所以，我的问题是 - 安排这个重复代码以减少混乱的最佳方法是什么？

基本原理

这个存储过程生成一个大的动态 SQL 并执行它。但是，它支持空运行，即显示 SQL 但不运行。因此，该过程将所有 SQL 语句累积到一个表中，然后将其输出或组成一个 SQL 字符串并执行它。但是，有一个问题，它是分阶段进行的。每个阶段都用所描述的重复但不完全相同的代码段分隔，因为@Phase在中间递增。

标签： sqlsql-server

解决方案

我通过在我的内部添加一个临时存储过程并调用它而不是重复块来解决它。

这是代码：

...
    DECLARE @Phase INT = 0

    SET @Sql = '
    CREATE PROCEDURE #ChangeClientIdSqlHelper(@Phase INT)
    AS
    BEGIN
        DECLARE @TableOfSqls TABLE (Id INT IDENTITY PRIMARY KEY, Part INT, Sql NVARCHAR(MAX))
        INSERT INTO @TableOfSqls VALUES (0, '''')
        INSERT INTO @TableOfSqls VALUES (0, ''TRUNCATE TABLE #Failure'')
        INSERT INTO @TableOfSqls VALUES (0, ''GO'')
        INSERT INTO @TableOfSqls VALUES (@Phase, '''')
        INSERT INTO @TableOfSqls VALUES (@Phase, ''USE ' + QUOTENAME(@dst) + ''')
        INSERT INTO @TableOfSqls VALUES (@Phase, ''SET XACT_ABORT ON'')
        INSERT INTO @TableOfSqls VALUES (0, ''IF EXISTS (SELECT 1 FROM #Failure) THROW 50000, ''''Skipped due to previous errors'''', 0'')
        INSERT INTO @TableOfSqls VALUES (0, ''INSERT INTO #Failure VALUES (0)'')
        INSERT INTO @TableOfSqls VALUES (0, '''')
        SELECT Part, Sql FROM @TableOfSqls ORDER BY Id
    END
    '
    EXEC(@Sql)
...
    SET @Phase = @Phase + 1
    INSERT INTO @TableOfSqls EXEC #ChangeClientIdSqlHelper @Phase
...
    SET @Phase = @Phase + 1
    INSERT INTO @TableOfSqls EXEC #ChangeClientIdSqlHelper @Phase
...

推荐阅读