4DotNet Developers Blog

Voor een uitbreiding van een website bij mijn opdrachtgever kreeg ik laatst het verzoek om een stored procedure te maken waarmee je documenten uit een database kunt vinden. Als zoekcriterium kun je hiervoor oa. een aantal woorden invullen dat binnen de tekst moet voorkomen. Voor deze stored procedure heb ik dynamisch SQL gebruikt.  Dynamisch heeft hier de betekenis dat je de query runtime opbouwt en dan pas uitvoert. Deze techniek zul je niet vaak gebruiken, omdat juist de kracht van stored procedures is dat deze vooraf gecompileerd zijn en daardoor snel het gewenste resultaat opleveren. Ook zul je hierbij bewust moeten zijn van het gevaar van SQL injection. Bij deze opdracht heb ik toch voor dynamische SQL gekozen, omdat het aantal zoekwoorden kan variëren van 0 tot meer dan 10.

De totale stored procedure (SP) is te lang om hier in z’n geheel weer te geven. Dit verhaal zou dan ook voor sommigen saai en langdradig worden en dat is nu net wat je niet verwacht bij een artikel met ‘Dynamisch’ in de titel. Om de vaart erin te houden, wil ik in dit blog daarom de belangrijkste onderdelen even kort benoemen.  Wie weet helpt het iemand op weg die ook met dynamisch SQL wil werken. Ik ben overigens best benieuwd naar andere voorbeelden van geschikte toepassingen van deze techniek.

Hieronder de belangrijkste onderdelen uit mijn dynamische SP.

Declareer een aantal interne variabelen binnen de SP:

DECLARE @SQL NVARCHAR(4000)
DECLARE @arrWords TABLE (RowNum INT IDENTITY (1,1) PRIMARY KEY NOT NULL, strField VARCHAR (255))
DECLARE @strField VARCHAR(255)
DECLARE @i INT
DECLARE @iMax INT

In mijn SP gebruik ik een parameter waarmee de zoekwoorden door een spatie gescheiden worden. Binnen de database was al een functie aanwezig om een Split mee uit te voeren (dbo.UDF_Split). Op het Internet zijn hiervan meerdere bruikbare voorbeelden te vinden die globaal hetzelfde resultaat opleveren:  een tabel met een record voor elk woord.  In de SP geeft dit de volgende regels:

INSERT INTO @arrWords
SELECT strWaarde
FROM dbo.UDF_Split (@strZoekwoorden, ' ')
SET @iMax = SELECT count(*) FROM @arrWords

De basis van het SQL statement binnen de SP is een eenvoudig SELECT statement. Dit ziet er zo uit:

SET @SQL = ' SELECT Titel, ID ' + CHAR(10) +
' FROM tblDocumenten WITH(NOLOCK) ' + CHAR(10) +
' WHERE Public = 1 '

Het echte dynamische gedeelte volgt hierop. Voor elk ingevoerde zoekwoord wil ik een AND regel toevoegen. Dit gebeurt met onderstaande code:

SET @i = 1
WHILE @i <= @iMax
   BEGIN
      SELECT @strField = strField
      FROM @arrWords
      WHERE RowNum = @i
      SET @SQL = @SQL + ' AND Titel LIKE ' + CHAR(39) + CHAR(37) + @strField + CHAR(37) + CHAR(39) + CHAR(10)
      SET @i = @i + 1
   END

Om de query die je op deze manier samenstelt uit te voeren, voeg je tenslotte de onderstaande regel toe:

EXEC sp_executesql @SQL

Op deze manier kun je een heel uitgebreid SQL statement opbouwen waarin echter steeds niet meer code staat dan je nodig hebt. In dit voorbeeld heb ik het expres eenvoudig gehouden, omdat ik alleen de essentie van deze techniek wil laten zien. De SP voor mijn opdrachtgever gaat nl.  nog een stapje verder, doordat er niet alleen in de titel van een document gezocht moet worden maar ook in de inhoud van het bijbehorende document. Hiervoor biedt de dynamische SQL ook uitkomst. Voor elk zoekwoord wordt een extra INNER JOIN aan de query toegevoegd.

Kleine tip voor wanneer je zelf met dynamische SQL aan de slag gaat: voeg tijdens de testfase de volgende regel toe aan je SP:

SELECT @SQL

Hiermee kun je het (tussen-)resultaat bekijken…

In mijn voorbeeld heb ik een aantal keer het woord CHAR gebruikt. Hiermee kun je bepaalde tekens aan je query toevoegen, zonder escapekarakters te gebruiken.

CHAR(10) RETURN
CHAR(34) dubbele quote
CHAR(37) procent-teken
CHAR(39) single quote

Stem of voeg toe aan :