Dynamic SQL Search Routines

NB: I write a lot of SQL targeting MSSql at work.

Apps I'm working on inevitably have a lot of procs that return data based on combinations of parameters, either search forms inside applications, or reports with optional parameters.

A few years back I found what I've considered the definitive treatise on correctly and performantly writing sql that handles multiple nullable parameters (recently updated to include SQL 2008 information)... I strongly recomend reading both versions.

I had reason to revisit it today, and re-found my old Golden Hammer... so I thought I'd post it here so I can find it easier next time.

Select Blah
From [tables]
Where (@Param is null OR Value = @Param)

Tends to be slow, avoid indexes, scrape then filter etc.

Select Blah
From [tables]
Where (@Param is not null AND Value = @Param)

Tends to be fast, index-happy, lean and mean.

Enjoy