SQL Tip

I've had to create a lot of dynamic queries in the past, and today I stumbled upon something so simple I can't believe I missed it before. Let's say you are doing a query with 100 parameters. In the past sometimes I would generate a dynamic sql statement either on the client side (less then desirable) or in a stored proc (still undesirable). Here is a simple technique that helps eliminate MOST (not all) instances where you need to do this.

set parameters that may or may not exist in the query to default to null in your stored proc.

Then you can do things like...

where
(mytable.value = @myparameter or myparameter is null)

Amazingly simple!

So I get a big Duhhh for not using this in a few projects in the past.