Geavanceerde logica in SQL met stored procedures, user-defined functions en triggers

Geschreven door: op woensdag 29 augustus 2018

Leestijd:

In SQL kan je met queries gegevens uit tabellen ophalen en bewerken. Daarnaast zijn er ook mogelijkheden en functies om data op te halen die niet uit tabellen komt, bijvoorbeeld de huidige datum. Soms vereisen de oplossingen echter dat je wat meer geavanceerde logica gebruikt, iets wat niet met één query is uit te voeren.

SQL biedt daarvoor in ieder geval de volgende drie mogelijkheden:

  1. Stored procedures
  2. User-defined Functions
  3. Triggers

Hieronder staat een korte uitleg van wat er mogelijk is met deze functies. Om meer te weten, kan je altijd verder lezen in de uitgebreide documentatie van Microsoft.

Stored Procedures

Met Stored Procedures kan je twee dingen doen: gegevens ophalen en gegevens bewerken. Dit doe je door een procedure uit te voeren: EXEC <procedurenaam> <parameters>. Een stored procedure kan van zichzelf alleen een int teruggeven, waarmee je bijvoorbeeld de status van de procedure (gelukt, mislukt) kan aangeven. Wil je een ander type variabele terugkrijgen, dan zal je deze als OUTPUT parameter mee moeten sturen: EXEC MijnProcedure @Variabele1 = ‘Test’, @Variabele2 OUTPUT

Je kan geen tables als variabelen teruggeven, maar je kan wel tables tonen door SELECT FROM te gebruiken in je procedure. Dit kunnen meerdere record sets zijn. Deze output kan dan gebruikt worden door je applicatie.

Stored Procedures op Microsoft Docs.

User-defined functions

User-defined functions zijn functies die je zelf hebt gemaakt en kan aanroepen in een select. GETDATE() bijvoorbeeld is een functie die de huidige datum en tijd teruggeeft, en kan je gebruiken in een SELECT statement, zoals SELECT GETDATE() as Datum; Dat kan je ook doen met functies die je zelf hebt gemaakt. Een veelgebruikte toepassing is het formatten van data: SELECT MijnOpmaakFunctie(name) as formattedName FROM blogs;

Je kan allerlei type variabelen teruggeven, maar ook tables. Een functie die een table teruggeeft, kan je handig gebruiken via een APPLY, om deze te combineren met een andere tabel.

Een onwaarschijnlijk voorbeeld:


CREATE OR ALTER FUNCTION GeeftTabelTerug (@EenZin nvarchar(50))
RETURNS TABLE AS
RETURN(
       SELECT Value as Woord FROM string_split(@EenZin, ' ')
);

GO

       SELECT Zin, T.Woord FROM
       (
             SELECT 'Dit is een zin' as Zin
             UNION ALL
             SELECT 'Hier staat een tweede zin' as Zin
       ) AS X

       CROSS APPLY GeeftTabelTerug(X.Zin) AS T

 

Bovenstaande voorbeeld geeft terug:

resultaat.jpg

(String_Split is een functie in SQL SERVER 2016.)

User-Defined Functions op Microsoft Docs.

Triggers

Een derde mogelijkheid om geavanceerde logica toe te passen, is via triggers. Met triggers kan je ervoor zorgen dat na een bewerking van een record in een table, automatisch bepaalde acties worden ondernomen. Je kan hier denken aan bijvoorbeeld uitgebreide validatie van ingevoerde gegevens, zoals toegestane postcodes, maar ook het niet meer kunnen toevoegen van gegevens als er al 5000 records in de table zitten.

Er zijn twee trigger-types:

  1. AFTER
  2. INSTEAD OF

AFTER-triggers worden na het uitvoeren van een UPDATE, DELETE of INSERT uitgevoerd en kan je gebruiken om data in te voeren in een andere tabel, om te loggen bijvoorbeeld, of je kan ervoor zorgen dat alle ingevoerde waardes altijd omgezet worden naar hoofdletters.

INSTEAD OF-triggers worden uitgevoerd in plaats van een UPDATE, DELETE of INSERT. De programmeur van de trigger kan logica toepassen om te bepalen of een actie door mag gaan en moet deze actie dan zelf code-matig in de trigger doorvoeren. Of je kan er zo voor zorgen dat bepaalde kolommen niet aangepast worden.

Triggers op Microsoft Docs.


Andere blogartikelen

  • HTML tips voor mail templates

    Geschreven door: op zaterdag 10 november 2018

    Bij het bouwen van een e-mail template binnen HTML komen er veel dingen kijken, ik leg in deze post een aantal basiselementen uit hoe je om moet gaan met de HTML van een mail template, en hoe wij daar ...

    Bekijk het artikel »
  • Documenteer je code

    Geschreven door: op zaterdag 27 oktober 2018

    Documenteren van je code is uiteraard een best practice. De code op zich mag zo beschrijvend mogelijk zijn, zodat de documentatie impliciet is, maar bijvoorbeeld een uitleg over het waarom van een bep ...

    Bekijk het artikel »
  • 5 tips voor een succesvolle website

    Geschreven door: op vrijdag 5 oktober 2018

    Binnen een gestroomlijnd B2B sales proces spelen websites en online strategieën een essentiële rol. Met de juiste aanpak bereikt u als ondernemer de doelgroep. Maar hoe creëert u het juiste effe ...

    Bekijk het artikel »
Bel 072 5345 888
Meer dan 40 bedrijven vertrouwen op ons
Allrig is de alles in een leverancier binnen de energie-industrie
Aliancys is een toonaangevend wereldwijd bedrijf actief in de verkoop van kwaliteitsharsen
ERIKS is een toonaangevende en innovatieve leverancier aan de procesindustrie en aan machinebouwers, die zowel de rol van specialist als die van brede MRO-leverancier vervult
Industrieel dienstverlener Heinen & Hopman Engineering uit Bunschoten is dé wereldwijde specialist op het gebied van klimaatbeheersing
Handicare is een internationale organisatie die ouderen helpt om hun dagelijks leven gemakkelijker te maken door het produceren van hoogwaardige trapliften
Onze Middelen en Technologieën
microsoft silver partner
Adobe partner
fd-gazellen-2018.jpg
Google analytics
partners-logo.jpg