Trzy metody API na procedurach SQL z walidacją dat i błędem jako zwykłym wynikiem

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

Opisujemy mechanizm z wdrożenia systemu awizacji Studio VSS.net w centrum dystrybucyjnym klienta. To przypadek jednego wdrożenia, a nie opis standardu produktu. Kod w artykule napisaliśmy od nowa na podstawie opisu mechanizmu, z nazwami przykładowymi, takimi jak widok_awizacji i procedura_raport_awizacji.

Wdrożenie udostępnia API raportowe w postaci trzech metod. Dokumentacja tych metod powstała w styczniu 2026 i była aktualizowana pod koniec miesiąca, a do każdej metody dołączono przykładowy plik żądania. Nie omawiamy tu warstwy dostępu do usługi. Zostajemy przy tym, co dzieje się w bazie: od JSON w parametrze do JSON w odpowiedzi. Platforma ma też rejestr wywołań API, który opisuje strona o tabeli _api_sql, ale ten artykuł dotyczy samych metod.

Procedura jako metoda API

Wywołanie metody jest żądaniem POST z ciałem w JSON. Ciało zawiera sekcję z parametrami metody. Każda metoda to osobna procedura SQL, uruchamiana przez wpis w repozytorium zapytań platformy. Procedura dostaje jeden parametr tekstowy z JSON, w którym leżą parametry metody, i zwraca JSON.

Dokumentacja opisuje trzy metody. Dwie z nich są raportami dostaw zrealizowanych i wymagają zakresu dat, trzecia nie przyjmuje parametrów.

Zestawienie metod według dokumentacji API. Status 9 oznacza awizację zrealizowaną.

Raport dostaw importowych zwraca pola z pięciu grup.

Pięć grup pól według dokumentacji API.

Odpowiedzi mają polskie nazwy pól. Dokumentacja przypisuje każdemu polu jeden z pięciu typów, wśród nich datę w zapisie RRRR-MM-DD i godzinę w zapisie GG:mm, a dla wybranych pól tekstowych podaje też długości. Awizacje importowe zasila w opisywanym wdrożeniu także import z pliku, który opisujemy w artykule o imporcie awizacji kontenerów z pliku.

Wąski widok z podpowiedzią NOLOCK

Wszystkie trzy metody czytają z jednego widoku. Widok ma tylko te kolumny, które trafiają do odpowiedzi, a tabele główne czyta z podpowiedzią NOLOCK. Celem jest to, żeby raport nie blokował pracy bramy i magazynu. Podzapytania słownikowe i załączniki leżą w samych procedurach i tej podpowiedzi nie mają.

CREATE VIEW dbo.widok_awizacji
AS
SELECT a.numer_awizacji,
       a.kierunek,
       a.rodzaj,
       a.status,
       a.data_awizacji,
       a.data_przyjazdu,
       a.data_wyjazdu,
       a.magazyn_stop,
       a.dostawca_id,
       r.kartony,
       r.sztuki
FROM dbo.awizacje AS a WITH (NOLOCK)
LEFT JOIN rozszerzenie.tabela_rozszerzenia AS r WITH (NOLOCK)
       ON r.numer_ref = a.numer_ref;

Przykład ilustracyjny. Nazwy obiektów i kolumn są wymyślone.

Dokumentacja podpowiedzi tabel opisuje NOLOCK jako synonim READUNCOMMITTED. Zapytanie nie zakłada wtedy blokad współdzielonych i nie czeka na blokady zapisu, ale może odczytać zmiany, których transakcja jeszcze nie zatwierdziła.

Przykładowy widok łączy tabelę główną z tabelą rozszerzenia, w której leżą kartony i sztuki. Budowę takiej tabeli opisujemy w artykule o kolumnach standardowych z nowym znaczeniem. Składnię samego widoku opisuje dokumentacja CREATE VIEW.

Metoda Awizacje oczekujące korzysta z tego samego widoku, ale nie ma parametrów. Jej zapytanie wybiera dostawy ze statusem 0 lub 1, bez zwrotów i wjazdów nieawizowanych, które nie mają zarejestrowanego wyjazdu. Dolną granicę wyznacza data sprzed dziesięciu dni, górnej nie ma.

SELECT w.numer_awizacji, w.status, w.data_awizacji
FROM dbo.widok_awizacji AS w
WHERE w.kierunek = 'IN'
  AND w.rodzaj NOT IN ('ZWR', 'WJN')
  AND w.status IN (0, 1)
  AND w.data_wyjazdu IS NULL
  AND w.data_awizacji >= DATEADD(DAY, -10, CAST(SYSDATETIME() AS date))
FOR JSON PATH;

Przykład ilustracyjny. Kody rodzajów ZWR i WJN oznaczają w przykładzie zwrot i wjazd nieawizowany.

Walidacja dat w procedurze

Metody raportowe wymagają dwóch pól, DateFrom i DateTo, w formacie RRRR-MM-DD. Wymóg formatu pochodzi z dokumentacji. Procedura sprawdza tylko, czy wartość da się zamienić na datę, i zwraca błąd w sytuacjach z tabeli.

Trzy warunki błędu i ich komunikaty według dokumentacji.

W przykładzie procedura wyjmuje oba pola z JSON funkcją JSON_VALUE, zamienia je na daty funkcją TRY_CONVERT i na końcu buduje odpowiedź. Polecenie SET NOCOUNT ON na początku wyłącza komunikaty o liczbie wierszy, które inaczej towarzyszyłyby każdej instrukcji.

CREATE PROCEDURE dbo.procedura_raport_awizacji
    @parametry nvarchar(max)
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @od_tekst nvarchar(50), @do_tekst nvarchar(50),
            @od date, @do date,
            @blad nvarchar(200), @wynik nvarchar(max);

    IF ISJSON(@parametry) = 1
    BEGIN
        SET @od_tekst = NULLIF(LTRIM(RTRIM(JSON_VALUE(@parametry, '$.DateFrom'))), N'');
        SET @do_tekst = NULLIF(LTRIM(RTRIM(JSON_VALUE(@parametry, '$.DateTo'))), N'');
    END;

    SET @od = TRY_CONVERT(date, @od_tekst);
    SET @do = TRY_CONVERT(date, @do_tekst);

    SET @blad = CASE
        WHEN @od_tekst IS NULL OR @do_tekst IS NULL
            THEN N'Parametry DateFrom i/lub DateTo nie mogą być puste'
        WHEN @od IS NULL OR @do IS NULL
            THEN N'Parametry DateFrom i/lub DateTo nie są poprawnego formatu daty'
        WHEN @od > @do
            THEN N'Wartość Data od nie może być większa od wartości Data do'
    END;

    IF @blad IS NOT NULL
    BEGIN
        SELECT (SELECT N'ERR' AS [Status], @blad AS [Message]
                FOR JSON PATH, WITHOUT_ARRAY_WRAPPER) AS wynik;
        RETURN;
    END;

    SET @wynik = (
        SELECT w.numer_awizacji AS [Numer awizacji],
               (SELECT d.nazwa FROM dbo.dostawcy AS d
                WHERE d.id = w.dostawca_id) AS [Dostawca],
               w.kartony AS [Kartony],
               w.sztuki AS [Sztuki],
               w.magazyn_stop AS [Stop magazynu]
        FROM dbo.widok_awizacji AS w
        WHERE w.kierunek = 'IN'
          AND w.status = 9
          AND w.magazyn_stop >= @od
          AND w.magazyn_stop < DATEADD(DAY, 1, @do)
        ORDER BY w.magazyn_stop, w.numer_awizacji
        FOR JSON PATH
    );

    SELECT ISNULL(@wynik, N'[]') AS wynik;
END;

Przykład ilustracyjny, napisany od nowa. Nie jest to kod z wdrożenia.

Trzy szczegóły tego kodu zasługują na osobne omówienie. Pierwszy to pusty tekst. Zamiana pustego tekstu na datę daje 1900-01-01, a nie niepowodzenie, więc przykład zamienia puste pole i same spacje na NULL, zanim spróbuje konwersji.

Drugi szczegół to format. Zapis RRRR-MM-DD jest dla typu date rozpoznawany niezależnie od ustawień językowych sesji, ale TRY_CONVERT przyjmie też inne zapisy, jeśli sesja je rozumie. Format z dokumentacji pilnuje więc dokumentacja, a nie procedura. Trzeci szczegół to data końcowa: porównanie z samą datą obcięłoby dzień końcowy o północy, więc zakres kończy warunek < DATEADD(DAY, 1, @do).

Przykład dokłada jeszcze sprawdzenie ISJSON. Tekst, który nie jest poprawnym JSON-em, kończy się w nim tym samym komunikatem co puste pola, zamiast wyjątku funkcji JSON.

Błąd jako zwykły wynik z kodem 200

Błąd walidacji nie jest wyjątkiem SQL. Procedura kończy się poleceniem RETURN i oddaje zwykły wynik JSON z dwoma polami: Status o wartości ERR oraz Message z opisem. Nagłówek odpowiedzi HTTP ma status 200 OK, a szczegóły błędu leżą poziom niżej, w treści odpowiedzi. Tak opisuje to dokumentacja.

Błąd walidacji wraca jako zwykły wynik z polami Status i Message. Nagłówek HTTP nadal ma status 200.

Dla klienta usługi z tego układu wynika jedna reguła: kod 200 nie mówi jeszcze, że raport się udał. Trzeba przeczytać treść. Sukces ma postać tablicy, a błąd obiektu, więc różnicę widać już po pierwszym znaku odpowiedzi.

Wybór między wyjątkiem a wynikiem jest decyzją projektu. Wyjątek przerywa wykonanie i trafia do warstwy, która wywołała procedurę. Wynik pozostaje zwykłą odpowiedzią i wędruje tą samą drogą co dane. W opisywanym wdrożeniu wybrano wynik.

Sukces bez koperty i pusty wynik

Odpowiedź udana nie ma koperty. Jest to tablica obiektów, po jednym obiekcie na awizację, zbudowana przez FOR JSON PATH. Klauzula tworzy obiekt z każdego wiersza, a aliasy kolumn stają się nazwami pól. Polskie nazwy w odpowiedzi wynikają więc wprost z aliasów w zapytaniu.

Kolejność obiektów w tablicy zależy od ORDER BY. Bez niego silnik nie gwarantuje żadnej, o czym piszemy szerzej w artykule o TOP bez ORDER BY.

Pusty wynik wymaga zastrzeżenia. W opisywanym wdrożeniu przy braku wierszy procedura zwraca NULL zamiast pustej tablicy. To wniosek z konstrukcji zapytania, niesprawdzony na działającej bazie, więc traktujemy go jako zastrzeżenie, a nie ustalenie. Przykład nie polega na tym zachowaniu: wynik zapytania trafia do zmiennej, a funkcja ISNULL zamienia brak odpowiedzi na pustą tablicę.

Pole z danymi przewoźnika pokazuje, jak NULL przenika przez łączenie tekstu. Zawiera ono znaczniki HTML, a do jego treści doklejany jest trzyznakowy skrót rodzaju. Pole ma wartość NULL, gdy brakuje przewoźnika albo telefonu kierowcy. Brak numeru auta lub nazwiska kierowcy daje natomiast puste miejsca w nawiasie.

Takie zachowanie odpowiada łączeniu tekstu operatorem plus, w którym część składników jest owinięta funkcją ISNULL, a część nie, bo zgodnie z ustawieniem CONCAT_NULL_YIELDS_NULL jeden NULL zeruje cały wynik. Odbiorca musi umieć odczytać oba przypadki.

Spóźnienie w pełnych godzinach zegarowych

Raport dostaw lokalnych ma pole spóźnienia. Zawiera ono tekst Brak spóźnienia albo liczbę godzin zapisaną jako tekst. Liczba godzin biegnie od początku okna awizacji, a dla kuriera od północy.

Godziny są zliczane jako pełne godziny zegarowe, a nie jako upływ czasu: przejście z 10:59 na 11:01 daje jedną godzinę. Taki wynik zwraca funkcja DATEDIFF z częścią HOUR, która liczy przekroczone granice godzin, a nie pełne sześćdziesiąt minut.

SELECT DATEDIFF(HOUR, '2026-03-02T10:59:00', '2026-03-02T11:01:00') AS godziny;

Przykład ilustracyjny. Wynik to 1, choć upłynęły dwie minuty.

Konsekwencja dotyczy każdego, kto zestawia te liczby z innymi miarami punktualności. Okno kuriera zaczyna się o północy, więc ta sama miara daje dla niego inne wartości niż dla dostawy z oknem godzinowym. Czasy etapów, z których powstają takie zestawienia, opisuje artykuł o znacznikach czasu etapów awizacji. Inne drogi raportowania z bazy, takie jak Reporting Services, omawia artykuł o raportowaniu z bazy SQL Server.

Pytania przed budową własnych metod

Przed przeniesieniem tego układu do własnego systemu odpowiedz na cztery pytania.

• Odbiorca: czy klient usługi odróżni błąd od sukcesu po treści, skoro kod HTTP jest zawsze taki sam.
• Pusty wynik: czy brak wierszy ma wracać jako pusta tablica, czy jako NULL, i kto tę odpowiedź przyjmie.
• Format dat: czy RRRR-MM-DD egzekwuje procedura, czy tylko dokumentacja.
• Odczyt: czy raport może czytać dane niezatwierdzone, jeśli widok korzysta z NOLOCK.

Ustawienia sesji, które procedura zapamiętuje z chwili zapisu, mogą zmienić jej działanie po wdrożeniu. Przykład takiego błędu opisujemy w artykule o QUOTED_IDENTIFIER OFF w procedurze.