Rory Primrose

Learn from my mistakes, you don't have time to make them yourself

View project on GitHub

Dynamic searching in TSQL

Posted on June 19, 2007

As I have been writing some SQL Server based software recently, I want to expose a standard way of providing searching capabilities for tables and views. The initial implementation started with a set of WHERE conditions like the following:

WHERE (FieldA = @FieldAValue OR @FieldAValue IS NULL)
(FieldB = @FieldBValue OR @FieldBValue IS NULL)

I now want to extend this functionality to include sorting and perhaps paging as well. I came across this article by Erland Sommarskog (SQL Server MVP) which address a ton of sorting variations.