Opret lagrede procedurer

6 minutter

Stored procedures er et af de mest kraftfulde værktøjer i SQL Server til at indkapsle forretningslogik og forbedre applikationsydelse. Når du opretter stored procedures, bygger du genanvendelige kodeblokke, der kører på serveren, hvilket reducerer netværkstrafikken og centraliserer dataadgangslogikken.

Forstå lagrede procedurer

En lagret procedure er en kompileret samling af T-SQL-sætninger, som SQL Server gemmer og udfører som en enkelt enhed. I modsætning til uplanlagte forespørgsler, som du sender til serveren hver gang, er lagrede procedurer forudkompilerede og optimerede, hvilket betyder, at de kører hurtigere ved efterfølgende udførelser.

Du bruger lagrede procedurer til at indkapsle kompleks forretningslogik, håndhæve datavalideringsregler og kontrollere, hvordan applikationer interagerer med din database. For eksempel kan du i stedet for at tillade direkte adgang til tabellerne oprette lagrede procedurer, der validerer input, anvender forretningsregler og logger ændringer, før du ændrer data.

Ydelsesfordelene kommer fra caching af forespørgselsplaner. Ved uplanlagte forespørgsler skal SQL Server parse og optimere hver forespørgsel hver gang. Med lagrede procedurer caches eksekveringsplanen efter første kørsel, hvilket reducerer overhead ved gentagne operationer.

Opret grundlæggende lagrede procedurer

Oprettelse af en lagret procedure starter med sætningen CREATE PROCEDURE efterfulgt af din T-SQL-logik. Du angiver procedurenavnet ved hjælp af en skema-kvalificeret identifikator, hvilket forbedrer klarhed og ydeevne.

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

Sætningen SET NOCOUNT ON forhindrer, at beskeden om antallet af berørte rækker sendes til klienten. Dette reducerer netværkstrafikken og forbedrer ydeevnen, især når proceduren udfører flere sætninger.

Når du opretter procedurer, skal du bruge og END nøgleordene BEGIN til klart at definere procedurens krop. Det gør din kode mere læsbar og hjælper med at forhindre fejl, når du tilføjer eller ændrer logik senere.

Arbejd med parametre

Parametre gør lagrede procedurer fleksible og genanvendelige. Du definerer inputparametre til at acceptere værdier fra den kaldende applikation og outputparametre til at returnere værdier til kalderen.

Inputparametre bruger @-symbolet efterfulgt af et parameternavn og datatype. Du kan angive standardværdier for at gøre parametre valgfrie:

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

Outputparametre lader dig returnere værdier til den kaldende applikation. Du definerer dem med nøgleordet OUTPUT :

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

Når du kalder en procedure med outputparametre, skal du erklære en variabel for at modtage værdien og bruge OUTPUT nøgleordet i sætningen EXECUTE .

Implementér fejlhåndtering

Robuste lagrede procedurer inkluderer fejlhåndtering for at håndtere uventede forhold og opretholde dataintegritet. Du implementerer fejlhåndtering ved hjælp af TRY...CATCH blokke, som fungerer på samme måde som undtagelseshåndtering i andre programmeringssprog.

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

Blokken TRY indeholder din hovedlogik, mens blokken håndterer CATCH eventuelle fejl, der opstår. Du kan bruge systemfunktioner som ERROR_MESSAGE(), ERROR_SEVERITY(), og ERROR_STATE() til at fange fejldetaljer og sende dem videre til den kaldende applikation.

Tjek @@TRANCOUNT altid, før du ruller transaktioner tilbage i blokken CATCH . Dette forhindrer fejl, hvis transaktionen allerede er gennemført eller aldrig er startet.

Anvend bedste praksis

At følge etablerede bedste praksisser, når du opretter lagrede procedurer, sikrer, at de er vedligeholdbare, sikre og yderlige.

Brug skema-kvalificerede navne

Brug skema-kvalificerede navne for alle objekter. Dette eliminerer tvetydighed og forbedrer ydeevnen ved at undgå overhead ved at løse skemaer:

-- Good
SELECT * FROM dbo.Orders

-- Avoid
SELECT * FROM Orders

Implementér parametervalidering

Implementér parametervalidering i starten af din procedure. Fejl hurtigt, når input er ugyldige i stedet for at behandle dårlige data:

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

Undgå `SELECT *`

Undgå SELECT * kode i produktionen. List eksplicit kolonner for at forhindre problemer, når tabelstrukturer ændres, og for at forbedre forespørgselsydelsen:

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

-- Avoid
SELECT * FROM dbo.Orders

Brug meningsfulde navne

Brug meningsfulde navne, der beskriver, hvad proceduren gør. Inkluder et verbum, der angiver operationen (Hent, Indsæt, Opdater, Slet, Beregn):

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

Undgå præfikset `sp_`

Brug ikke præfikset sp_ til dine lagrede procedurer. SQL Server reserverer dette præfiks til systemprocedurer, der er gemt i databasen master . Når du navngiver en procedure med sp_, søger master SQL Server først, før den tjekker den aktuelle database, hvilket tilføjer unødvendig overhead:

-- Good
CREATE PROCEDURE dbo.GetCustomerOrders

-- Avoid
CREATE PROCEDURE dbo.sp_GetCustomerOrders

At bygge videre på disse praksisser hjælper dig med at skabe lagrede procedurer, som dit team kan forstå, vedligeholde og stole på til at udføre pålideligt i produktionsmiljøer.

Feedback

Var denne side nyttig?