SQL How to ignore string parameter
Posted on 2010-09-23
I have a select statement, where I pass in a parameter (@Specialty) this comes in as a comma separated list of values and I convert these to a table variable and use it like so.
Where Specialty in (Select Value From dbo.fnParmsToList(@Specialty)).
What I want is to ignore this value if it is null, (or empty string I can force it to either value in the calling code)
I usually use coalesce to return the first non null value, and this works great for single parameter values, but I cant get it to work with the above.
So my question is how can I ignore the parameter @Specialty and return ALL values unless I specify the parameter.
If I might just add, this is not the only value in the where clause, there are 5 others, where I use coalesce in 3.