There is nothing that annoys me like writing large, nested SQL queries. After adding a 7-th RIGHT OUTER JOIN I want to scream in pain. But on the other hand a few things in programming give me this instant feeling of accomplishment when a well written query returns what was expected (and in a good time too!)
I recently found this hack that made my life easier. It's one of those things I didn't know, and probably should have. It goes like this:
Instead of writing:
(...) WHERE field IN (1,2,3,4,5,6,7,8,9)
OR:
(...) WHERE field BETWEEN @start AND @end
it can be also achieved by:
(...) WHERE CHARINDEX( ',' + field + ',', ',' + @listOfValues + ',' ) > 0
It simply checks whether the value of 'field' is in the coma-separated list of values, that can be supplied as a parameter to a stored procedure.
The context (simplified): the stored procedure previously took two parameters - @start and @end and returned all records where 'field' was between @start and @end. Pretty common scenario. But after a while my cliend reqested that instead of a range of values, he wants to be able to specify an arbitrary list of values. Can be 1 value, but can as well be a 100 of them.
By constructing a coma separated list of values and submitting that list as a parameter to the stored proc I was able to minimize the amount of changes to the stored proc - basically I just needed to remove the @end parameter, and change the @start to @listOfValues. The code hack above did the rest of the work for me.
Performance issues? Using CHARINDEX is similar to using LIKE. In my case - where the expected max number of values in the list did not exceed 100, it is negligible.
