Klonowanie wiersza konfiguracji w T-SQL: dynamiczna lista kolumn i bezpieczniki THROW

Autor: Adam Siemiątkowski, SoftwareStudio Group Sp. z o.o.

Klonowanie wiersza w T-SQL z INSERT SELECT ma sens, gdy nowa rola ma dostać konfigurację taką jak istniejąca, a schemat tabeli może się zmienić. W przykładzie wdrożeniowiec ma skonfigurowaną rolę MAG1 i potrzebuje takiej samej konfiguracji dla roli MAG2. Skrypt sam składa listę kolumn z katalogu, więc dodanie kolumny do tabeli nie wymaga zmiany skryptu.

Nowy wiersz konfiguracji najprościej zrobić jako kopię wiersza wzorcowego. Listę kolumn pobieramy z sys.columns, pomijając kolumny, których nie wolno wstawiać. Skrypt zatrzymuje się przez THROW, gdy brakuje wzorca albo podmiana nie trafiła, drugie uruchomienie niczego nie zmienia, a przebieg próbny kończy się ROLLBACK.

To wzorzec z nazwami przykładowymi. Sprawdziliśmy go na SQL Server 2022 przy czterech poziomach zgodności z zakresu od 110 do 160 i wynik był za każdym razem ten sam. Przebieg próbny wykonujemy na bazie, na której wolno uruchamiać skrypt.

Nowy wiersz konfiguracji powstaje z kopii wiersza wzorcowego

Tabela konfiguracji roli ma dziesięć kolumn, wymienionych w pierwszym bloku poniżej. Wśród nich są numer z IDENTITY i kolumna obliczana, wersja ma typ rowversion, a kolumna Utworzono wartość domyślną SYSDATETIME().

Indeks unikatowy filtrowany obejmuje parę nazwa roli i kod pozycji dla wierszy aktywnych. Dane próbne to trzy wiersze roli MAG1 i jeden wiersz roli ROLA3. Sam indeks filtrowany opisuje artykuł o indeksie filtrowanym i miękkim usuwaniu, a tutaj jest tylko tłem jednej z pułapek.

CREATE TABLE dbo.KonfiguracjaRoli (
    Id               int            IDENTITY(1,1) NOT NULL PRIMARY KEY,
    NazwaRoli             varchar(20)    NOT NULL,
    KodPozycji              varchar(30)    NOT NULL,
    Etykieta            nvarchar(100)  NOT NULL,
    Porzadek        int            NOT NULL,
    [Opis dodatkowy] nvarchar(200)  NULL,
    Aktywna          bit            NOT NULL CONSTRAINT DF_Konf_Akt DEFAULT (1),
    Utworzono        datetime2(0)   NOT NULL CONSTRAINT DF_Konf_Utw DEFAULT (SYSDATETIME()),
    Wersja           rowversion     NOT NULL,
    KluczPozycji            AS (NazwaRoli + '/' + KodPozycji)
);
CREATE UNIQUE INDEX UX_KonfiguracjaRoli ON dbo.KonfiguracjaRoli (NazwaRoli, KodPozycji) WHERE Aktywna = 1;
GO
INSERT dbo.KonfiguracjaRoli (NazwaRoli, KodPozycji, Etykieta, Porzadek, [Opis dodatkowy], Utworzono) VALUES
 ('MAG1',  'PRZYJECIE',  N'Przyjęcie',  10, N'ekran przyjęcia', '2026-01-05'),
 ('MAG1',  'WYDANIE',    N'Wydanie',    20, NULL,               '2026-01-05'),
 ('MAG1',  'SPIS',       N'Spis',       30, N'tylko kierownik', '2026-01-05'),
 ('ROLA3', 'ZLECENIE',   N'Zlecenie',   10, NULL,               '2026-01-05');

Przykład ilustracyjny. Wszystkie nazwy w przykładzie są wymyślone. GO rozdziela partie poleceń.

Lista kolumn pochodzi z katalogu sys.columns

Zamiast wpisywać kolumny ręcznie, czytamy je z sys.columns dla tabeli, w kolejności column_id. Pomijamy cztery rodzaje kolumn, wymienione w tabeli poniżej. Każda nazwa przechodzi przez QUOTENAME.

Przykład. Sposób obsługi kolumn tabeli konfiguracji z poprzedniego bloku.

Z jednego zapytania pomocniczego powstają dwie listy: kolumny docelowe i wartości. W liście wartości nazwa roli i etykieta są zastąpione parametrami @NowaRola i @NowaEtykieta, a pozostałe kolumny kopiujemy z wiersza wzorcowego.

Obie listy składa FOR XML PATH('') z .value('.', 'nvarchar(max)'). Sprawdziliśmy to na SQL Server 2022 przy poziomach zgodności od 110 do 160, a starszych silników nie sprawdzaliśmy. Funkcja STRING_AGG z WITHIN GROUP (ORDER BY column_id), dostępna od SQL Server 2017 (według dokumentacji), zbudowała listę w tej samej postaci. Nad wartościami nvarchar(4000) kończy się ona błędem 9829, gdy wynik przekracza 8000 bajtów, więc dla długich list rzutujemy wartości na nvarchar(max).

INSERT SELECT przechodzi przez sp_executesql z parametrami

Nazwę tabeli do polecenia bierzemy z katalogu: QUOTENAME(OBJECT_SCHEMA_NAME(id)) + '.' + QUOTENAME(OBJECT_NAME(id)). Wartości przekazujemy jako parametry sp_executesql, a nie sklejamy w tekst. QUOTENAME(N'Opis dodatkowy') daje [Opis dodatkowy], a QUOTENAME(N'a]b') daje [a]]b], więc nazwa z nawiasem nie rozbija polecenia.

SET XACT_ABORT ON;
DECLARE @Wzorzec int = 1, @NowaRola varchar(20) = 'MAG2', @NowaEtykieta nvarchar(100) = N'Przyjęcie (wariant B)', @Zatwierdz bit = 0;
DECLARE @id int = OBJECT_ID(N'dbo.KonfiguracjaRoli'), @tabela nvarchar(300), @kolumny nvarchar(max), @wartosci nvarchar(max), @sql nvarchar(max), @maxId int;
SET @tabela = QUOTENAME(OBJECT_SCHEMA_NAME(@id)) + N'.' + QUOTENAME(OBJECT_NAME(@id));
WITH k AS (
    SELECT c.column_id, QUOTENAME(c.name) AS Nazwa,
           CASE c.name WHEN N'NazwaRoli' THEN N'@NowaRola' WHEN N'Etykieta' THEN N'@NowaEtykieta' ELSE QUOTENAME(c.name) END AS Wartosc
    FROM sys.columns AS c
    WHERE c.object_id = @id AND c.is_identity = 0 AND c.is_computed = 0
      AND c.system_type_id <> 189 AND c.name <> N'Utworzono')
SELECT @kolumny  = STUFF((SELECT N', ' + Nazwa   FROM k ORDER BY column_id FOR XML PATH(''), TYPE).value('.', 'nvarchar(max)'), 1, 2, N''),
       @wartosci = STUFF((SELECT N', ' + Wartosc FROM k ORDER BY column_id FOR XML PATH(''), TYPE).value('.', 'nvarchar(max)'), 1, 2, N'');

Przykład ilustracyjny. Skrypt, część 1: lista kolumn i wartości. Obie części skryptu tworzą jedną partię poleceń, a @Zatwierdz = 0 oznacza przebieg próbny.

Skrypt klonujący ma zatrzymać się głośno, gdy wzorca brakuje, i niczego nie zmieniać, gdy kopia już istnieje.

Bezpieczniki THROW zatrzymują skrypt, a drugie uruchomienie niczego nie zmienia

Cztery bezpieczniki mają własne numery. THROW 50100 zatrzymuje skrypt, gdy lista kolumn jest pusta, a 50101, gdy brak wiersza wzorcowego. THROW 50102 reaguje, gdy @@ROWCOUNT po INSERT jest różny od 1, a 50103, gdy po wstawieniu nie ma dokładnie jednego nowego wiersza z nową rolą i nową etykietą.

W sprawdzeniu wywołano 50101 (brak wzorca) i 50103 (wyzwalacz dopisał drugi wiersz; transakcja wycofana, stan bez zmian). Bezpieczniki 50100 i 50102 nie zostały uruchomione w przykładzie. Przy SET XACT_ABORT ON błąd zgłoszony przez THROW w otwartej transakcji wycofuje ją. Zgłaszanie błędu po ROLLBACK opisuje artykuł o TRY CATCH i THROW w procedurze zapisu.

IF @kolumny IS NULL THROW 50100, N'Nie odczytano kolumn tabeli.', 1;
IF NOT EXISTS (SELECT 1 FROM dbo.KonfiguracjaRoli WHERE Id = @Wzorzec) THROW 50101, N'Brak wiersza wzorcowego.', 1;
IF EXISTS (SELECT 1 FROM dbo.KonfiguracjaRoli w JOIN dbo.KonfiguracjaRoli x ON x.NazwaRoli = @NowaRola AND x.KodPozycji = w.KodPozycji WHERE w.Id = @Wzorzec)
BEGIN PRINT N'Wiersz już istnieje, pomijam.'; RETURN; END;
SELECT @maxId = MAX(Id) FROM dbo.KonfiguracjaRoli;
SET @sql = N'INSERT ' + @tabela + N' (' + @kolumny + N') SELECT ' + @wartosci + N' FROM ' + @tabela + N' WHERE Id = @Wzorzec;';
BEGIN TRAN;
EXEC sp_executesql @sql, N'@Wzorzec int, @NowaRola varchar(20), @NowaEtykieta nvarchar(100)', @Wzorzec, @NowaRola, @NowaEtykieta;
IF @@ROWCOUNT <> 1 THROW 50102, N'Klonowanie nie dodało dokładnie jednego wiersza.', 1;
IF (SELECT COUNT(*) FROM dbo.KonfiguracjaRoli WHERE Id > @maxId) <> 1
   OR NOT EXISTS (SELECT 1 FROM dbo.KonfiguracjaRoli WHERE Id > @maxId AND NazwaRoli = @NowaRola AND Etykieta = @NowaEtykieta)
    THROW 50103, N'Podmiana wartości nie trafiła w nowy wiersz.', 1;
IF @Zatwierdz = 1 COMMIT; ELSE BEGIN ROLLBACK; PRINT N'Przebieg próbny: zmiany wycofane.'; END;

Przykład ilustracyjny. Skrypt, część 2: bezpieczniki THROW, a potem transakcja z decyzją COMMIT albo ROLLBACK.

Gdy istnieje już wiersz nowej roli o kodzie wzorca, skrypt wypisuje „Wiersz już istnieje, pomijam.” i kończy bez zmian. Kolejne uruchomienie po zatwierdzeniu, w przykładzie trzecie, nie zmieniło liczby wierszy roli MAG2: nadal 1. Uruchomienie z @Wzorzec = 99 kończy się błędem 50101 z komunikatem „Brak wiersza wzorcowego.”, a stan tabeli się nie zmienia.

Przebieg próbny kończy się ROLLBACK

Parametr @Zatwierdz = 0 kończy transakcję przez ROLLBACK i komunikat „Przebieg próbny: zmiany wycofane.”, a stan zostaje bez zmian: MAG1 ma 3 wiersze, a ROLA3 jeden. Parametr @Zatwierdz = 1 dodaje jeden wiersz roli MAG2 z etykietą „Przyjęcie (wariant B)”. Porządek i opis dodatkowy pochodzą ze wzorca, data utworzenia z wartości domyślnej, a wersja i klucz pozycji są nowe albo przeliczone.

SELECT NazwaRoli, COUNT(*) AS Wierszy FROM dbo.KonfiguracjaRoli GROUP BY NazwaRoli ORDER BY NazwaRoli;

Przykład ilustracyjny. Zapytanie kontrolne po każdym przebiegu.

Numer identity nie wraca po wycofaniu: wiersz wstawiony po wycofanej próbie dostał numer 6 zamiast 5. Luka w numeracji nie jest błędem, więc ciągłości numerów nie należy zakładać. Liczba wierszy na rolę przed próbą i po niej jest taka sama, a po zatwierdzeniu przybywa tylko MAG2 (1). Tę samą regułę egzekwuje warunek w THROW 50103.

Zmianę treści istniejących wierszy, z kontrolą skrótu, a nie dodanie nowego wiersza, opisuje artykuł o zmianie zapytań zapisanych w tabeli konfiguracji.

Sesja sqlcmd bez przełącznika -I przerywa skrypt błędem 1934

Narzędzie sqlcmd bez opcji -I uruchamia sesję z QUOTED_IDENTIFIER OFF. Wtedy zapytanie z metodą XML .value() kończy się błędem 1934 (SELECT failed), a zwykły INSERT do tabeli z indeksem filtrowanym też błędem 1934 (INSERT failed). Z opcją -I sesja ma ustawienie ON, jak w SSMS.

sqlcmd -S <serwer> -d <baza> -E -b -I -f 65001 -i plik.sql

Przykład ilustracyjny. Opcja -b przerywa po błędzie, -I włącza QUOTED_IDENTIFIER, a -f 65001 czyta plik jako UTF-8. Plik zapisujemy z BOM.

Bez -I także polecenie CREATE UNIQUE INDEX z danych próbnych kończy się błędem 1934, więc dane próbne zakładamy w sesji z -I. Drugi z tych przypadków, INSERT na tabeli z indeksem filtrowanym, opisuje szerzej wspomniany wyżej artykuł o indeksie filtrowanym, a procedurę zapisaną z QUOTED_IDENTIFIER OFF artykuł o procedurze z QUOTED_IDENTIFIER OFF.

NULL w poleceniu i obcięta zmienna nie zgłaszają błędu

Przy błędnej nazwie tabeli lista kolumn jest NULL, więc cały tekst polecenia też, a sp_executesql z NULL niczego nie wykonuje i nie zgłasza błędu. Dlatego pierwszy bezpiecznik sprawdza listę kolumn.

Druga pułapka dotyczy długości. Lista kolumn o 7804 znakach, zapisana do zmiennej nvarchar(4000), została po cichu obcięta do 4000, więc zmienne na listę i na polecenie muszą być nvarchar(max). Nie sprawdzaliśmy kolumn okresu tabel temporalnych ani kolumn szyfrowanych, więc nie twierdzimy, że lista z sys.columns obsługuje każdy rodzaj kolumny.

Pytania przed napisaniem własnego klonowania wiersza

Zanim przeniesiesz wzorzec do własnej bazy, odpowiedz sobie na cztery pytania.

• Kolumny: które kolumny konfiguracji mają się zmienić w kopii, a które mają dostać wartość domyślną albo nową wartość nadaną przez silnik.
• Ponowne uruchomienie: czy skrypt ma być uruchamiany więcej niż raz i co ma się wtedy stać z istniejącym wierszem.
• Przebieg próbny: kto i na jakiej bazie wykonuje przebieg próbny przed zatwierdzeniem.
• Kontrola: jak sprawdzisz po zatwierdzeniu, że konfiguracja innych ról nie została zmieniona.

Bezpieczne składanie nazw przez QUOTENAME w innym zastosowaniu opisuje artykuł o podstawianiu pól w szablonie wiadomości. Wszystkie artykuły sekcji zebraliśmy w spisie artykułów o SQL Server.