Vytvoření uložených procedur

Uložené procedury jsou jedním z nejvýkonnějších nástrojů SQL Serveru pro zapouzdření obchodní logiky a zlepšení výkonu aplikací. Při vytváření uložených procedur vytváříte opakovaně použitelné bloky kódu, které se spouští na serveru, což snižuje síťový provoz a centralizuje logiku přístupu k datům.

Pochopení uložených procedur

Uložená procedura je kompilovaná kolekce příkazů T-SQL, které SQL Server ukládá a spouští jako jednu jednotku. Na rozdíl od neplánovaných dotazů, které odesíláte na server pokaždé, se uložené procedury předkompilují a optimalizují, což znamená, že při následných spuštěních běží rychleji.

Uložené procedury slouží k zapouzdření složité obchodní logiky, vynucení pravidel ověření dat a řízení způsobu interakce aplikací s databází. Místo povolení přímého přístupu k tabulce můžete například vytvořit uložené procedury, které ověřují vstup, použijí obchodní pravidla a změny protokolu před úpravou dat.

Výhody výkonu pocházejí z ukládání plánů dotazů do mezipaměti. Při neplánovaných dotazech musí SQL Server analyzovat a optimalizovat každý dotaz pokaždé. U uložených procedur se plán provádění ukládá do mezipaměti po prvním spuštění a snižuje režijní náklady na opakované operace.

Vytvořte základní uložené procedury

Vytvoření uložené procedury začíná příkazem CREATE PROCEDURE následovaným logikou T-SQL. Název procedury zadáte pomocí identifikátoru, k němuž je připojeno schéma, což zlepšuje srozumitelnost a výkon.

CREATE PROCEDURE dbo.GetCustomerOrders
AS
BEGIN
    SET NOCOUNT ON;
    
    SELECT 
        OrderID,
        CustomerID,
        OrderDate,
        TotalAmount
    FROM dbo.Orders
    ORDER BY OrderDate DESC;
END

Příkaz SET NOCOUNT ON zabraňuje odeslání zprávy o počtu ovlivněných řádků klientovi. To snižuje síťový provoz a zlepšuje výkon, zejména když procedura provádí více příkazů.

Při vytváření procedur použijte klíčová slova BEGIN a END k jasnému definování těla procedury. Díky tomu je váš kód čitelnější a pomáhá zabránit chybám při pozdějším přidání nebo úpravě logiky.

Práce s parametry

Parametry umožňují flexibilní a opakovaně použitelné uložené procedury. Definujete vstupní parametry pro příjem hodnot z volající aplikace a výstupní parametry pro vrácení hodnot zpět volajícímu.

Vstupní parametry používají symbol @ následovaný názvem parametru a datovým typem. Můžete zadat výchozí hodnoty, aby byly parametry volitelné:

CREATE PROCEDURE dbo.GetCustomerOrdersByDate
    @CustomerID int,
    @StartDate datetime = NULL,
    @EndDate datetime = NULL
AS
BEGIN
    SET NOCOUNT ON;
    
    SELECT 
        OrderID,
        OrderDate,
        TotalAmount
    FROM dbo.Orders
    WHERE CustomerID = @CustomerID
        AND (@StartDate IS NULL OR OrderDate >= @StartDate)
        AND (@EndDate IS NULL OR OrderDate <= @EndDate)
    ORDER BY OrderDate DESC;
END

Výstupní parametry umožňují vracet hodnoty do volající aplikace. Definujete je pomocí klíčového OUTPUT slova:

CREATE PROCEDURE dbo.CalculateOrderTotal
    @OrderID int,
    @TotalAmount decimal(10,2) OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    
    SELECT @TotalAmount = SUM(Quantity * UnitPrice)
    FROM dbo.OrderDetails
    WHERE OrderID = @OrderID;
    
    RETURN 0;
END

Při volání procedury s výstupními parametry musíte deklarovat proměnnou pro příjem hodnoty a použití klíčového OUTPUTEXECUTE slova v příkazu.

Implementace zpracování chyb

Robustní uložené procedury zahrnují zpracování chyb pro správu neočekávaných podmínek a zachování integrity dat. Implementujete zpracování chyb pomocí TRY...CATCH bloků, které fungují podobně jako zpracování výjimek v jiných programovacích jazycích.

CREATE PROCEDURE dbo.InsertCustomerOrder
    @CustomerID int,
    @OrderDate datetime,
    @TotalAmount decimal(10,2)
AS
BEGIN
    SET NOCOUNT ON;
    
    BEGIN TRY
        BEGIN TRANSACTION;
        
        -- Validate customer exists
        IF NOT EXISTS (SELECT 1 FROM dbo.Customers WHERE CustomerID = @CustomerID)
        BEGIN
            RAISERROR('Customer does not exist.', 16, 1);
        END
        
        -- Insert order
        INSERT INTO dbo.Orders (CustomerID, OrderDate, TotalAmount)
        VALUES (@CustomerID, @OrderDate, @TotalAmount);
        
        COMMIT TRANSACTION;
        RETURN 0;
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0
            ROLLBACK TRANSACTION;
        
        DECLARE @ErrorMessage nvarchar(4000) = ERROR_MESSAGE();
        DECLARE @ErrorSeverity int = ERROR_SEVERITY();
        DECLARE @ErrorState int = ERROR_STATE();
        
        RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState);
        RETURN -1;
    END CATCH
END

Blok TRY obsahuje vaši hlavní logiku, zatímco blok CATCH zpracovává všechny chyby, ke kterým dochází. Můžete použít systémové funkce, jako je ERROR_MESSAGE(), ERROR_SEVERITY()a ERROR_STATE() zachytit podrobnosti o chybě a předat je volající aplikaci.

Vždy zkontrolujte @@TRANCOUNT před vrácením transakcí v bloku CATCH. Tím se zabrání chybám, pokud transakce již byla dokončena nebo nebyla nikdy spuštěna.

Použití osvědčených postupů

Když vytváříte uložené procedury, postupujte podle zavedených osvědčených postupů, abyste zajistili jejich údržbu, zabezpečení a výkon.

Používejte názvy kvalifikované schématem

Pro všechny objekty použijte kvalifikované názvy schématu. Tím se eliminuje nejednoznačnost a zvyšuje výkon, protože se vyhnete nákladům na zpracování schématu.

-- Good
SELECT * FROM dbo.Orders

-- Avoid
SELECT * FROM Orders

Implementace ověřování parametrů

Na začátku procedury implementujte ověření parametru. Selžte okamžitě, pokud jsou vstupní data neplatná, místo zpracování chybných dat.

IF @CustomerID IS NULL OR @CustomerID <= 0
BEGIN
    RAISERROR('CustomerID must be a positive integer.', 16, 1);
    RETURN -1;
END

Vyhněte se SELECT *

Vyhněte se používání SELECT * v produkčním kódu. Explicitně vypisovat sloupce, které brání problémům při změně struktur tabulek a zvýšení výkonu dotazů:

-- Good
SELECT OrderID, CustomerID, OrderDate FROM dbo.Orders

-- Avoid
SELECT * FROM dbo.Orders

Použití smysluplných názvů

Použijte smysluplné názvy, které popisují, co tento postup dělá. Uveďte sloveso, které označuje operaci (Get, Insert, Update, Delete, Calculate):

CREATE PROCEDURE dbo.GetActiveCustomersByRegion
CREATE PROCEDURE dbo.UpdateCustomerAddress
CREATE PROCEDURE dbo.DeleteExpiredOrders

Vyhněte se předponě sp_

Nepoužívejte předponu sp_ pro uložené procedury. SQL Server si tuto předponu vyhrazuje pro systémové procedury uložené v master databázi. Při pojmenování procedury pomocí sp_ SQL Server nejprve prohledá master, než zkontroluje aktuální databázi, čímž přidává zbytečnou režii:

-- Good
CREATE PROCEDURE dbo.GetCustomerOrders

-- Avoid
CREATE PROCEDURE dbo.sp_GetCustomerOrders

Budování na těchto postupech vám pomůže vytvořit uložené procedury, které váš tým může pochopit, udržovat a důvěřovat jim, že budou spolehlivě fungovat v produkčních prostředích.