In SSRS 2005,must declare the scalar variable

Question

I have a report that runs a stored proc:

EXEC ra_spProjectCalendar @Month, @Year, @ProjectID

ProjectID is A multi-select dropdown. When a single project is selected, it works fine. If I select multiple projects, I get the error:

"Must declare scalar variable "@ProjectID"

It works fine when I run it from the Data tab, however, when I put it into Preview mode I have problems.

Answer 1

I got something similar to work by constructing the query as an expression - in your example

="EXEC ra_spProjectCalendar @Month = '" & Parameters!Month.Value & "',@Year='" & Parameters!Year.Value & "',@ProjectID = '" &  Join(Parameters!ProjectID.Value, ",") & "'"

Some code in the target SP splits the multi-select string into a temporary table which is used in joins to create output.

I can't remember why we ended up doing it this way - it was right at the start of our move to SSRS and things have moved on a bit since - but it may have been the problem you describe.