Блог

У меня есть следующая хранимая процедура:

 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 что увеличивается в середине.

Я решил эту проблему, добавив временную хранимую процедуру внутри моей и вызвав ее вместо повторяющегося фрагмента.

Вот код:

 ...
    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
...
 

Для ваших целей вы можете определить вторую временную таблицу:

 DECLARE @Repeats TABLE (Id INT IDENTITY PRIMARY KEY, Part INT, Sql NVARCHAR(MAX))

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

Затем вы можете вставить это, когда захотите:

 INSERT INTO @TableOfSqls (sql)
    SELECT part, sql
    FROM @Repeats
    ORDER BY id;
 

Я собираюсь добавить, что разбиение SQL построчно кажется любопытным подходом к написанию кода. Я не уверен на 100%, что динамический SQL вообще необходим. Если это так, вы можете сохранить весь блок кода (вместе с символами новой строки) в одной строке.