在SSRS 2005中，必须声明标量变量
我有一个运行存储过程的报告：
EXEC ra_spProjectCalendar @Month, @Year, @ProjectID
ProjectID 是一个多选下拉列表。 当选择单个项目时，它工作得很好。 如果我选择多个项目，则会收到错误：
"Must declare scalar variable "@ProjectID"
当我从“数据”选项卡运行它时，它工作正常，但是，当我将其置于预览模式时，我遇到了问题。
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.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
我通过将查询构造为表达式来获得类似的工作 - 在您的示例中，
目标 SP 中的某些代码将多选字符串拆分为临时表，该临时表在联接中用于创建输出。
我不记得为什么我们最终会这样做 - 就在我们开始转向 SSRS 时，事情已经发生了一些变化 - 但这可能是你所描述的问题。
I got something similar to work by constructing the query as an expression - in your example
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.
我发生了同样的错误：必须声明标量变量。
我现在意识到首先要检查的是，当您以 text 形式输入查询或以存储过程类型输入存储过程时，ssrs 会自动在数据集属性的参数选项卡中创建参数条目。
如果它不创建它们，它就无法正确读取您的查询。
就我而言，我使用 OPENQUERY 调用分析服务多维数据集并引用包含大量方括号的字段名。 不幸的是，SSRS 喜欢重新格式化您的代码，以便格式良好的“[Measures].[SomeMeasure]”变成这种丑陋的转义字符混乱
[[Measures]].[Somemeasure]]]
如果您在另一个工具中编辑该查询然后将其重新复制回 ssrs，则 ssrs 无法读取该语法！ 即使它生成它！ 因此，删除所有多余的方括号并替换双引号，然后它会再次工作并生成您的参数。
I had the same error occur: Must declare scalar variable.
I realise now the first thing to check is that when you enter the query as text , or the stored proc as a stored proc type, that ssrs automatically creates the paramter entry in the parameters tab of the dataset properties.
If it doesn't create them, it can't read your query properly.
In my case I was using OPENQUERY to call an analysis services cube and referencing the fieldnames which contain a lot of square brackets. Unfortunately SSRS loves to reformat your code so that nicely formated "[Measures].[SomeMeasure]" turns into this ugly escape character mess
[[Measures]].[Somemeasure]]]
If you edit that query in another tool then re-copy it back to ssrs, then ssrs can't read that syntax! Even though it generates it! So remove all the extra square brackets and replace the double quotes and boom it works again and generates your parameters.