SQL Stored Procedure: Automatiseer en optimaliseer queries

SQL-stored procedures zijn sets SQL-instructies die in een database worden opgeslagen. Ze kunnen op aanvraag worden uitgevoerd om taken voor datamanipulatie en -validatie uit te voeren, waardoor je minder vaak herhaald SQL-werk hoeft te schrijven voor veelvoorkomende bewerkingen. Stored procedures zijn handig in databasebeheer omdat ze efficiëntie en hergebruik bevorderen. Daarnaast ondersteunen ze verbeterde databasebeveiliging en onderhoudbaarheid. In dit artikel bespreken we hoe je SQL-stored procedures maakt en uitvoert, veelvoorkomende use-cases en best practices.

Als start raad ik je van harte de cursussen Introduction to SQL en Learn SQL aan om de basiskennis van het extraheren en analyseren van data met SQL op te doen. Ook de SQL Basics Cheat Sheet, die je kunt downloaden, is een handige referentie met de meest gebruikte SQL-functies.

Wat is een stored procedure in SQL?

Een stored procedure in SQL is een verzameling SQL-instructies die in de database wordt opgeslagen. Het doel van een SQL-stored procedure is om een reeks bewerkingen op een database uit te voeren, zoals data opvragen, invoegen, bijwerken of verwijderen.

In tegenstelling tot reguliere SQL-queries, die als losse commando’s worden uitgevoerd, kapselen stored procedures een set SQL-instructies in, waardoor je de code eenvoudig kunt hergebruiken zonder steeds opnieuw SQL-commando’s te schrijven.

De voordelen van SQL-stored procedures zijn onder andere:

• Hergebruik van code: zodra een stored procedure is gemaakt, kun je die zo vaak aanroepen als nodig is, waardoor redundantie in SQL-code wordt voorkomen.
• Betere prestaties: stored procedures voeren vaak sneller uit omdat ze vooraf gecompileerd en op de databaseserver opgeslagen zijn, wat netwerkvertraging en compilatietijd vermindert.
• Beveiliging: stored procedures kunnen de databeveiliging en controle over toegang tot gevoelige data verbeteren door gebruikers toestemming te geven een procedure uit te voeren zonder directe toegang tot tabellen.

Basis-syntax en -structuur

De syntax voor het maken van een stored procedure kan per databasesysteem iets verschillen (bijv. MySQL, SQL Server, Oracle). Hieronder staat een algemeen voorbeeld met SQL Server-syntax:

-- Create a stored procedure named ProcedureName
CREATE PROCEDURE ProcedureName 
    @Parameter1 INT, 
    @Parameter2 VARCHAR(50)  
AS
BEGIN
    -- SQL statements go here
    SELECT * FROM TableName WHERE Column1 = @Parameter1 AND Column2 = @Parameter2;
END;Mogelijk gemaakt door

In bovenstaande syntax:

• CREATE PROCEDURE: dit commando gebruik je om een nieuwe stored procedure te definiëren.

• ProcedureName: de naam die aan de stored procedure wordt gegeven. Deze moet uniek zijn binnen de database.

• @Parameter1, @Parameter2: parameters zijn optioneel; hiermee kan de procedure invoerwaarden ontvangen. Elke parameter wordt gedefinieerd met een @-symbool en een gegevenstype (bijv. INT, VARCHAR(50)).

• AS BEGIN...END: de SQL-instructies binnen BEGIN en END vormen de body van de procedure, waar de hoofdlógica wordt uitgevoerd. In dit voorbeeld haalt de procedure records op uit een tabel op basis van specifieke voorwaarden.

Input- en outputparameters

Met input- en outputparameters kun je waarden naar en van een stored procedure doorgeven.

Als @Parameter1 bijvoorbeeld als inputparameter is gedefinieerd, kan deze bij het aanroepen van de procedure elke waarde krijgen, wat invloed heeft op de SQL-logica of de output. In onderstaand voorbeeld haalt de parameter @UserID gegevens op die specifiek zijn voor de opgegeven UserID.

-- Create a procedure to retrieve data for a specific user by UserID
CREATE PROCEDURE GetUserData 
 -- Input parameter: ID of the user to retrieve
    @UserID INT 
AS
BEGIN
    -- Select all columns from Users where UserID matches the input parameter
    SELECT * FROM Users WHERE UserID = @UserID;
END;Mogelijk gemaakt door

Outputparameters, gedefinieerd met het sleutelwoord OUTPUT, laten een stored procedure een waarde teruggeven aan de aanroepende omgeving. Als een procedure bijvoorbeeld een korting berekent, kan die met een outputparameter worden teruggegeven aan het aanroepende programma.

-- Create a procedure to calculate the discounted price
CREATE PROCEDURE CalculateDiscount 
    @Price DECIMAL(10, 2), 
    @DiscountRate DECIMAL(5, 2),  
    @FinalPrice DECIMAL(10, 2) OUTPUT -- Output: final price after discount
AS
BEGIN
    -- Calculate final price by applying the discount rate to the original price
    SET @FinalPrice = @Price * (1 - @DiscountRate);
END;Mogelijk gemaakt door

Om deze procedure aan te roepen, gebruik je:

-- Declare a variable to store the final price after discount
DECLARE @FinalPrice DECIMAL(10, 2);
-- Execute the CalculateDiscount procedure with a price of 100 and a 10% discount 
-- Store the output in the @FinalPrice variable
EXEC CalculateDiscount @Price = 100, @DiscountRate = 0.1, @FinalPrice = @FinalPrice OUTPUT;
-- Select and display the final discounted price
SELECT @FinalPrice AS FinalPrice;Mogelijk gemaakt door

Stored procedures in MySQL

Zoals gezegd kun je een stored procedure in SQL op verschillende manieren uitvoeren, afhankelijk van het databasesysteem en de gebruikte tools.

Stored procedures maken in MySQL

Een stored procedure in MySQL maken houdt in dat je de naam, parameters en de SQL-instructies definieert die de body vormen. In het volgende voorbeeld maken we een procedure genaamd GetEmployeeDetails die EmployeeID als inputparameter aanneemt en gegevens ophaalt voor die specifieke medewerker.

DELIMITER $
-- Create a procedure to retrieve details for a specific employee by EmployeeID
CREATE PROCEDURE GetEmployeeDetails(IN EmployeeID INT)  
BEGIN  
    -- Select all columns from Employees where EmployeeID matches the input parameter  
    SELECT * FROM Employees WHERE EmployeeID = EmployeeID;  
END$
DELIMITER ;Mogelijk gemaakt door

Stored procedures uitvoeren in MySQL

De meest gebruikelijke manier om een stored procedure uit te voeren is met SQL-commando’s. In MySQL gebruiken we het CALL-commando om de stored procedure uit te voeren.

CALL ProcedureName();Mogelijk gemaakt door

Met de gedefinieerde procedure GetEmployeeDetails ziet de uitvoerquery er als volgt uit:

-- Execute the stored procedure to retrieve details for EmployeeID 101
CALL GetEmployeeDetails(101);Mogelijk gemaakt door

Stored procedures in SQL Server

SQL Server biedt specifieke syntax en commando’s voor het maken, uitvoeren en beheren van stored procedures. Deze aanpak maakt het eenvoudiger om efficiënte en herbruikbare SQL-routines te bouwen die complexe taken met minimale herhaling aankunnen.

Stored procedures maken in SQL Server

Een stored procedure in SQL Server maken houdt in dat je de naam, parameters en de SQL-instructies definieert die de body vormen. In het volgende voorbeeld maken we een procedure genaamd GetEmployeeDetails die @EmployeeID als inputparameter aanneemt en gegevens ophaalt voor die specifieke medewerker.

-- Create a procedure to retrieve details for a specific employee by EmployeeID
CREATE PROCEDURE GetEmployeeDetails
    @EmployeeID INT          -- Input parameter: ID of the employee to retrieve
AS
BEGIN
    -- Select all columns from Employees where EmployeeID matches the input parameter
    SELECT * FROM Employees WHERE EmployeeID = @EmployeeID;
END;Mogelijk gemaakt door

Stored procedures uitvoeren in SQL Server

In SQL Server roep je een stored procedure aan met het commando EXEC of EXECUTE. Onderstaand voorbeeld laat zien hoe je de stored procedure GetEmployeeDetails uitvoert met specifieke inputparameters. 

-- Execute the GetEmployeeDetails procedure with the EmployeeIDset to 102
EXEC GetEmployeeDetails @EmployeeID = 102;Mogelijk gemaakt door

Je kunt ook outputparameters gebruiken door de variabele in het commando te declareren. In het volgende voorbeeld is @TotalSales gedeclareerd als variabele om de output van CalculateTotalSales te ontvangen. 

-- Declare a variable to store the total sales amount
DECLARE @TotalSales DECIMAL(10, 2);
-- Execute CalculateTotalSales for SalespersonID 5, store the result in @TotalSales
EXEC CalculateTotalSales @SalespersonID = 5, @TotalSales = @TotalSales OUTPUT;
-- Display the total sales amount
SELECT @TotalSales AS TotalSales;Mogelijk gemaakt door

Ik raad je onze cursus Introduction to SQL Server aan om de verschillende functionaliteiten van SQL Server voor het opvragen van data te begrijpen. Kijk ook naar onze volledige SQL Server Developer carrièretrack, die je niet alleen de skills geeft om stored procedures te maken, bij te werken en uit te voeren, maar je ook helpt met aggregatiefuncties, joins, tabellen invoegen en verwijderen, en veel meer.

Veelvoorkomende toepassingen van stored procedures

SQL-stored procedures zijn nuttig in scenario’s waarin repetitieve, complexe taken nodig zijn. Hieronder staan praktijksituaties waarin stored procedures worden toegepast in databeheer en bedrijfsprocessen. 

Datavalidatie en handhaving van integriteit

Stored procedures kunnen worden gebruikt om data te valideren vóór een update of insert. In het onderstaande voorbeeld controleert een stored procedure of het e-mailadres van een klant uniek is voordat een nieuw record in de tabel Customers wordt ingevoegd, om dataconsistentie te waarborgen. Dit centraliseert de validatielogica in de database, vermindert redundantie en zorgt voor uniforme handhaving over verschillende applicaties heen.

-- Create a procedure to add a new customer, checking for duplicate email
CREATE PROCEDURE AddCustomer
    @CustomerName VARCHAR(50), 
    @CustomerEmail VARCHAR(50)
AS
BEGIN
    -- Check if the email already exists in the Customers table
    IF EXISTS (SELECT 1 FROM Customers WHERE Email = @CustomerEmail)
        -- Throw an error if the email is already in use
        THROW 50000, 'Email already exists.', 1;
    ELSE
        -- Insert new customer details if email is unique
        INSERT INTO Customers (Name, Email) VALUES (@CustomerName, @CustomerEmail);
END;Mogelijk gemaakt door

Geautomatiseerde dataverwerking en rapportage

Je kunt stored procedures ook gebruiken om periodieke rapporten te genereren of grote datasets te verwerken. Een stored procedure kan bijvoorbeeld dagelijkse verkoopdata van een e-commerceplatform aggregeren en opslaan in een rapportagetabel, zodat teams eenvoudiger verkoopinzichten kunnen krijgen zonder complexe queries te draaien.

-- Create a procedure to generate a daily sales report
CREATE PROCEDURE GenerateDailySalesReport
AS
BEGIN
    -- Insert today's date and total sales into the SalesReport table
    INSERT INTO SalesReport (ReportDate, TotalSales)
    
    -- Select current date and sum of sales for today from Sales table
    SELECT CAST(GETDATE() AS DATE), SUM(SalesAmount) 
    FROM Sales 
    WHERE SaleDate = CAST(GETDATE() AS DATE);
END;Mogelijk gemaakt door

Transactiebeheer

Met stored procedures kun je ervoor zorgen dat meerdere bewerkingen als één transactie worden uitgevoerd. In een banksysteem kan een stored procedure bijvoorbeeld zowel de debet- als de creditactie bij een overboeking afhandelen, zodat beide acties samen slagen of mislukken.

-- Create a procedure to transfer funds between accounts
CREATE PROCEDURE TransferFunds
    @SenderAccount INT,
    @ReceiverAccount INT,
    @Amount DECIMAL(10, 2)
AS
BEGIN
    BEGIN TRANSACTION;  -- Start a transaction to ensure atomicity
    -- Deduct the specified amount from the sender's account balance
    UPDATE Accounts SET Balance = Balance - @Amount WHERE AccountID = @SenderAccount;
    -- Add the specified amount to the receiver's account balance
    UPDATE Accounts SET Balance = Balance + @Amount WHERE AccountID = @ReceiverAccount;
    -- Check for errors and rollback if any occurred; otherwise, commit the transaction
    IF @@ERROR <> 0
        ROLLBACK TRANSACTION;  -- Undo all changes if an error occurred
    ELSE
        COMMIT TRANSACTION;    -- Confirm changes if no errors
END;Mogelijk gemaakt door

Toegangscontrole en databeveiliging

Je kunt SQL-stored procedures ook gebruiken om de toegang tot gevoelige informatie te beperken. Een stored procedure kan bijvoorbeeld directe tabeltoegang inperken door gebruikers alleen een procedure te laten aanroepen die uitsluitend relevante velden, zoals saldi, ophaalt zonder transactiedetails.

-- Create a procedure to retrieve account balance, with authorization check
CREATE PROCEDURE GetAccountBalance
    @AccountID INT, 
    @UserID INT 
AS
BEGIN
    -- Check if the account exists and is owned by the specified user
    IF EXISTS (SELECT 1 FROM Accounts WHERE AccountID = @AccountID AND UserID = @UserID)
        -- If authorized, select and return the account balance
        SELECT Balance FROM Accounts WHERE AccountID = @AccountID;
    ELSE
        -- If unauthorized, throw an error
        THROW 50000, 'Unauthorized access.', 1;
END;Mogelijk gemaakt door

Datamigratie en ETL-processen

Stored procedures worden ook gebruikt om data te laden, transformeren en migreren tussen systemen. Een stored procedure kan het extraheren van data uit een brondatabase automatiseren, deze naar wens transformeren en invoegen in een doeltabel, waardoor dataintegratie voor rapportage of analyse eenvoudiger wordt.

CREATE PROCEDURE ETLProcess
AS
BEGIN
    -- Extract
    INSERT INTO StagingTable
    SELECT * FROM SourceTable WHERE Condition;
    -- Transform
    UPDATE StagingTable SET ColumnX = TransformationLogic(ColumnX);
    -- Load
    INSERT INTO TargetTable
    SELECT * FROM StagingTable;
END;Mogelijk gemaakt door

Best practices voor stored procedures

Het schrijven van efficiënte en onderhoudbare stored procedures zorgt ervoor dat je database optimaal presteert. Hieronder vind je tips voor het schrijven van stored procedures voor je SQL-databases.

• Gebruik consistente naamgevingsconventies: maak stored procedures herkenbaar en begrijpelijk met een consistente, beschrijvende naamgeving. Vermijd bovendien het sp_-prefix in SQL Server, dat is gereserveerd voor systeemprocedures, om mogelijke conflicten en prestatieproblemen te voorkomen.

• Implementeer foutafhandeling: omsluit SQL-instructies met TRY...CATCH-blokken om fouten op te vangen en af te handelen en dataintegriteit te behouden. 

• Optimaliseer voor prestaties: minimaliseer het gebruik van cursors, want die kunnen traag en resource-intensief zijn. Gebruik in plaats daarvan set-gebaseerde operaties, die doorgaans efficiënter zijn. Indexeer ook vaak gebruikte kolommen en vermijd complexe joins in grote tabellen om het geheugengebruik te verminderen en de efficiëntie te verbeteren.

• Parameteriseer stored procedures: gebruik parameters in plaats van hardgecodeerde waarden, zodat je dynamische waarden aan je procedure kunt doorgeven en deze flexibeler en herbruikbaarder wordt.

Bekijk onze cursus Intermediate SQL om meer te leren over het gebruik van aggregatiefuncties en joins om data te filteren. Probeer ook onze skill-tracks SQL Server Fundamentals en SQL Fundamentals om je vaardigheden in het joinen van tabellen en data-analyse te verbeteren.

Conclusie

SQL-stored procedures verbeteren het hergebruik van code en de prestatieoptimalisatie in databasebeheer. Stored procedures versterken ook de databasebeveiliging via gecontroleerde toegang en door dataintegriteit te waarborgen. Als dataprofessional moedig ik je aan om stored procedures te maken en uit te voeren om best practices in databasebeheer onder de knie te krijgen.

Als je een vaardige data-analist wilt worden, bekijk dan onze carrièretrack Associate Data Analyst in SQL om de benodigde skills te leren. De cursus Reporting in SQL is ook geschikt als je wilt leren hoe je professionele dashboards bouwt met SQL. Tot slot raad ik aan om de SQL Associate Certification te behalen om je beheersing van SQL voor data-analyse aan te tonen en je te onderscheiden van andere dataprofessionals.