In the past, I have avoided this issue by creating a dummy value for the SSRS parameter and making it the default value. Usually, I would display this to the user (i.e., set the label for the value) as "-ALL-" or something like that and set the value to something that would never be an actual value in the database. For example, for a State parameter that accepts two-letter abbreviations for U.S. states, I would set the value to 'XX' and the label to "-ANY-" or "-ALL-". Then, the query syntax in the stored procedure would look like this:

WHERE @State = 'XX' OR @State = State

This makes it certain that the value you pass to the stored proc parameter from SSRS will yield the desired result. Be sure to comment the stored proc code to explain the significance and use of the dummy value.

I also have used a little trick to make sure the dummy value appears at the top of the drop-down list. I wrote the query to populate the drop-down list like this:

select

StateName as paramLabel

,StateAbbr as paramValue

,2 as ordinal

from dbo.States

union all

select '-ALL-', 'XX', 1

order by ordinal, paramLabel

I used the artificial ordinal column to force the dummy value to the top of the list.

A friendly suggestion: If you haven't read Gail Shaw's article on "catch-all" queries, it's worth the time. She outlines the performance pitfalls of query syntax like that you cite above. Dynamic SQL most likely will improve the performance of your query when you want to allow some input parameters to be NULL. Of course, dynamic SQL may not be possible in your environment, so if not, please ignore this suggestion!

Unfortunately, SSRS 2005 passes the selected values from a drop-down list for a multi-valued parameter to SQL Server as a comma-delimited string. That's why your "State in (@State)" syntax doesn't work - to SQL Server, it looks like "State in ('TN, AR, MS')", which of course is not the desired syntax of "State in ('TN', 'AR', 'MS')". You have to parse the comma-delimited string to get the individual values. Jeff Moden has developed a very efficient way to do this:

http://www.sqlservercentral.com/articles/Tally+Table/72993/

After you have created the "splitter" function, your query syntax would look like this:

WHERE State in (select Item from dbo.DelimitedSplit8K(@State, ','))

If you need to refer to the values contained in @State several times in the stored proc, you could also insert the results of the subselect above into a table variable or temporary table so that you only have to call the splitter function once.

Hope that helps!