visual-studio 针对 ODBC 数据源使用 MS Reporting Services (SQL Server 2008) 中的参数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9275/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Using Parameters in MS Reporting Services (SQL Server 2008) against an ODBC data source
提问by N8g
I writing a report in Visual Studio that takes a user input parameter and runs against an ODBC datasource. I would like to write the query manually and have reporting services replace part of the where clause with the parameter value before sending it to the database. What seems to be happening is that the @parmNameI am assuming will be replaced is actually being sent as part of the SQL statement. Am I missing a configuration setting somewhere or is this simply not possible?
我在 Visual Studio 中编写了一份报告，该报告采用用户输入参数并针对 ODBC 数据源运行。我想手动编写查询并让报告服务在将其发送到数据库之前用参数值替换部分 where 子句。似乎正在发生的是，@parmName我假设将被替换的实际上是作为 SQL 语句的一部分发送的。我是否在某处缺少配置设置，或者这根本不可能？
I am not using the filter option in the tool because this appears to bring back the full dataset from the database and do the filtering on the SQL Server.
我没有在工具中使用过滤器选项，因为这似乎可以从数据库中带回完整的数据集并在 SQL Server 上进行过滤。
回答by Jorriss
It sounds like you'll need to treat the SQL Statement as an expression. For example:
听起来您需要将 SQL 语句视为表达式。例如：
="Select col1, col2 from table 1 Where col3 = " & Parameters!Param1.Value
If the where clause is a string you would need to do the following:
如果 where 子句是字符串，则需要执行以下操作：
="Select col1, col2 from table 1 Where col3 = '" & Parameters!Param1.Value & "'"
Important: Do not use line breaks in your SQL expression. If you do you will get an error.
重要提示：不要在 SQL 表达式中使用换行符。如果你这样做，你会得到一个错误。
Holla back if you need any more assistance.
如果您需要更多帮助，Holla 回来。
回答by Matt Hamilton
Doesn't ODBC use the old "?" syntax for parameters? Try this:
ODBC 不使用旧的“？” 参数的语法？尝试这个：
select col1, col2 from table1 where col3 = ?
The order of your parameters becomes important then, but it's less vulnerable to SQL injection than simply appending the parameter value.
那么参数的顺序就变得很重要，但与简单地附加参数值相比，它更不容易受到 SQL 注入的影响。
回答by H.G.
Encountered same problem trying to query an access database via ODBC.
尝试通过 ODBC 查询访问数据库时遇到同样的问题。
My original query: SELECT A.1 FROM A WHERE A.1 = @parameterresulted in error. Altered to: SELECT A.1 FROM A WHERE A.1 = ?.
我的原始查询：SELECT A.1 FROM A WHERE A.1 = @parameter导致错误。改为：SELECT A.1 FROM A WHERE A.1 = ?。
You then have to map the query parameter with your report parameter.
然后，您必须将查询参数与您的报告参数进行映射。
回答by stjohnroe
I am a bit confused about this question, if you are looking for simple parameter usage then the notation is :*paramName*, however if you want to structurally change the WHEREclause (as you might in sql+ using ?) then you should really be using custom code within the report to define a function that returns the required sql for the query.
我对这个问题有点困惑，如果您正在寻找简单的参数用法，那么符号是 : *paramName*，但是如果您想从结构上更改WHERE子句（就像您在 sql+ 中使用 ?），那么您真的应该在其中使用自定义代码定义一个函数的报告，该函数返回查询所需的 sql。
Unfortunately, when using custom code, parameters cannot be referenced directly in the generated query but have to have there values concatenated into the resultant String, thus introducing the potential for SQLinjection.
不幸的是，当使用自定义代码时，不能在生成的查询中直接引用参数，而必须将这些值连接到结果字符串中，从而引入SQL注入的可能性。
