Er zijn stapels prutserijen in allerlei programma’s die de mensen een stapel werk zouden besparen mochten ze ze kennen. Photoshop is zo’n notoir programma dat vol met shortcuts en handigheidjes zit, en ook de Microsoft Office-programma’s lopen over van de nuttige features die niemand praktisch gebruikt.

Maar ook dingen als T-SQL,  dat nochtans een relatief beperkte lijst toch wel goed gedocumenteerde functies heeft, die met andere woorden op een relatief beperkte tijd allemaal eens zelf te doorlopen en uit te proberen zijn, bevat zaken die het leven veel gemakkelijker zouden kunnen maken maar dat toch wellicht vaak niet doen wegens onbekend en dus onbemind.

[phew, ik ben zo content dat ik door die zin geraakt ben, dat het me eigenlijk zelfs niet al te veel meer kan schelen of hij nog begrijpbaar is] 🙂

Stel er moeten op een website events getoond worden, afhankelijk van een aantal mogelijke factoren.  Bijvoorbeeld dat alle events van een bepaald type getoond moeten worden en/of van een bepaalde dag en/of van een bepaald departement.

Naieve structuur van de database:

• tblEvents
  • ID
  • title
  • deptID
  • typeID
  • eventdate
  • description

Dat geeft, euh, acht mogelijke combinaties:

1. select * from tblEvents
2. select * from tblEvents where deptID=[dept]
3. select * from tblEvents where typeID=[type]
4. select * from tblEvents where eventdate=[date]
5. select * from tblEvents where typeID=[type] and eventdate=[date]
6. select * from tblEvents where deptID=[dept] and eventdate=[date]
7. select * from tblEvents where typeID=[type] and deptID=[dept]
8. select * from tblEvents where typeID=[type] and deptID=[dept] and eventdate=[date]

In het programmeren wordt dat al snel iets als

queryString="select * from tblEvents"
if ([type] not null OR [dept] not null OR [date] nor null) then
    queryString=queryString & " where 1=1 "
    if ([type] not null) then
        queryString=queryString & " and typeID="&[type]
    end if
    if ([dept] not null) then
        queryString=queryString & " and deptID="&[dept]
    end if
    if ([date] not null) then
        queryString=queryString & " and eventdate="&[date]
    end if
end if

Mja. Beetje vies natuurlijk. Dat soort dingen kan ook in de database zelf gebeuren. Eén mogelijkheid is met case when (zie bijvoorbeeld alhier voor een voorbeeldje), maar in dit geval gaat het nog veel sneller met coalesce:

select * from tblEvents where
  typeID=coalesce([type],typeID) and
  deptID=coalesce([dept],deptID) and
  eventdate=coalesce([date],eventdate)

Ha! Die coalesce aanvaardt een onbeperkt aantal parameters en geeft de eerste niet-NULL-waarde terug.

Dus: als [type] en [dept] niet meegegeven werden en [date] wel, dan wordt de query hierboven

select * from tblEvents where
  typeID=typeID and
  deptID=deptID and
  eventdate=[date]

Dat zou kunnen opgebouwd worden in een query en zo, maar nóg veel gemakkelijker (om niet te zeggen properder) is het om het allemaal in een stored procedure te smijten. Dat wordt dan iets als

create procedure getEvents
@type int=null, @dept int=null, @date datetime=null as
select * from tblEvents where
typeID=coalesce(@type,typeID) and
deptID=coalesce(@dept,deptID) and
eventdate=coalesce(@date,eventdate)

…en dan proper schone aanroepen als bijvoorbeeld exec getEvents voor alle evenementen, en exec getEvents @deptID=5 voor alle evenementen van departement 5, en exec getEvents @typeID=3, @dept=6 voor alle evenementen van type 3 en departement 6.

Proper, mijn gedacht.