Specify Query Parameters

• Apr 24, 2020
• 4 minutes to read

Each query in an SqlDataSource contains a collection of query parameters. A query parameter holds an external value that is inserted into an SQL statement before the query is executed. This value can be static, if specified explicitly, or dynamic, when an expression is evaluated to obtain a value.

The query parameter value is inserted into the SQL query string at the “@QueryParameterName” placeholder’s position.

Use query parameters to:

• Filter report data at the data source level

• Specify parameters for a stored procedure

Use the query’s Parameters property to access the query parameters collection. These include parameters created in the Query Builder and generated for the data source’s stored procedure. Use the SelectQuery.FilterString property to access the query’s filter string.

You can add query parameters in the Query Parameters dialog and modify the filter in the FilterString Editor.

Configure Query Parameters

The following properties are available for a query parameter:

• Name

• Type

• Expression

• Value

Pass a Multi-Value Parameter Value to a Query

You can bind query parameters to multi-value report parameters.

For instance, the following query selects orders whose IDs are listed in the @OrderIDs query parameter.

1. Create a multi-value report parameter.

2. Invoke the Query Builder. Set the query parameter value to the expression that contains the report’s multi-value parameter.

3. In the Query Builder , invoke the Filter Editor and specify a filter string that includes the newly created query parameter.

If the multi-value report parameter accepts null values and its value is not pre-specified, set the following filter string:

In this case, all data is initially loaded to the report data source. When a user selects report parameter values, the report data source is filtered.

Pass a Multi-Value Parameter Value to a Stored Procedure

You cannot pass a multi-value parameter value to a stored procedure directly. Use one of the following expression functions:

• Use the Join() expression function to convert the array of parameter values to a string if you use MS SQL Server, MySQL or Oracle database systems.

• Use the CreateTable() expression function to prepare a table using values of several multi-value parameters.