Vandaag moest ik in SQL 2005 een functie maken met wat functionaliteit. Uiteindelijk had ik maar bedacht dat er gebruik gemaakt moest worden van een tijdelijke tabel waar m’n tijdelijke informatie in werd geplaatst. Vanuit die tijdelijke tabel kon ik dan weer extra mutaties en selecties doen om zo tot het uiteindelijke resutlaat te komen.

Nu ben ik niet enorm slecht in SQL en dacht ook mooi op weg te zijn. Alle functie functionaliteit had ik eerst in een query geschreven, zodat er eenvoudig getest kon worden of alles werkte. In m’n query kreeg ik nu het gewenste resultaat. De exacte code had ik gekopieerd naar een lege functie wilde ik de functie aanmaken. Kreeg ik ineens een fout dat er onjuiste tekens in m’n functie stond. Kan gebeuren natuurlijk. Een verkeerde komma of letter maakt zelfs van Jezus een ketter. De hele functie nogmaals nalopen, maar ik kon niet echt een fout vinden. Na wat proberen en debuggen kreeg ik wel een foutmelding dat je geen tabel mocht aanmaken in een functie. Dat vond ik persoonlijk toch wel de meest debiele fout die ik kon bedenken en kon het ook niet geloven. SQL draait om tabellen en dan kun je niet eens een tijdelijke aanmaken in een functie. Daar moet dan weer een Stored Procedure voor worden aangemaakt.

Door even te Googlen kwam ik er achter dat je toch wel een tijdelijke tabel aan kunt maken in een functie, maar moet het net even iets anders. Blijkbaar kun je in SQL gebruik maken van verschillende tijdelijke tabellen. De volledige uitleg is hier te vinden: https://databases.aspfaq.com/database/should-i-use-a-temp-table-or-a-table-variable.html In het kort komt het hier op neer. Een lokale tijdelijke tabel maak je op deze manier.

CREATE TABLE #people ( id INT, name VARCHAR(32) )

Deze tijdelijke #people-tabel blijft de gehele sessie bestaan. Daarna verdwijnt de tabel. Ook kun je hem uiteraard gewoon droppen. Een globale tijdelijke tabel maak je op deze manier (dus met twee #-tekens)

CREATE TABLE ##people ( id INT, name VARCHAR(32) )

Het verschil met een lokale tijdelijke tabel zit hem in het feit dat een globale tijdelijke tabel in alle sessies kan worden gezien en gebruikt, totdat de sessie welke de tabel heeft aangemaakt afsluit. Dit kan natuurlijk handig zijn als je hem op meerdere plaatsen moet gebruiken. En dan de laatste, welke ik nodig had, is de tabel variabele.

DECLARE @people TABLE ( id INT, name VARCHAR(32) )

Deze tabel variabelen verdwijnen, zodra je uit de scope gaat waar deze in is aangemaakt. Ook is dit de meest efficiente tabel, aangezien hij in het geheugen blijft en dus eigenlijk geen I/O operaties nodig heeft.
Dit laatste was bij mij nog niet bekend, van die I/O operaties. Nu blijkt dat je alleen maar tabel-variabelen mag gebruiken in een SQL functie. De andere tabel-vormen mogen niet worden gebruikt. Nadat ik dit wist kon ik m’n query herschrijven en eindelijk de functie aanmaken die ik oorspronkelijk had willen maken. De uiteindelijke functie opmaak ziet er nu ongeveer zo uit:

CREATE FUNCTION [dbo].[GetNieuweItems] () RETURNS @TempNieuweItems 
TABLE ( ItemID INT, Tekst NVARCHAR(255) ) AS 
BEGIN 
    DECLARE @TempItems TABLE ( NieuwItemID INT, NieuwItemTekst NVARCHAR(255) ) 
    INSERT INTO @TempItems 
        SELECT ItemID, Tekst 
        FROM Tabel INSERT INTO @TempNieuweItems SELECT DISTINCT(NieuwItemID), NieuwItemTekst FROM @TempItems WHERE condities... 
    RETURN 
END;

Toch weer iets nuttigs geleerd vandaag.