Specify Query Parameters

• Jul 01, 2021
• 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

  The Query Builder helps you construct SQL queries to create a data-bound report or bind a report to an SQL data source,

  

  You can also add or edit queries in an SQL data source.

  

  Click Edit Parameters. Configure query parameters in the invoked Query Parameters dialog.

  

  Click Filter to invoke the Filter Editor where you can specify filter conditions that include the created query parameters.

  

  The specified filter conditions are added as an SQL statement’s WHERE part.

  The Filter button is not available for custom SQL queries. Specify the query parameter in the query instead.

  

  Refer to the Filter Data at the Data Source Level topic for more information.

• Specify parameters for a stored procedure

  The Report Wizard and Data Source Wizard include the Create a query or select a stored procedure page. If you select a stored procedure, the wizard creates a query parameter for each procedure parameter and allows you to configure the created parameters on the Configure query parameters and preview the result page.

  

  See Bind a Report to a Stored Procedure for more information.

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

  The parameter name.

• Type

  The parameter value’s data type.

• Expression

  Determines whether the parameter value is static or dynamic.

• Value

  Specifies the parameter value. If the Expression option is enabled, this property contains an expression that is parsed and processed to obtain the parameter value. An expression can include data source fields or report parameters.

Pass a Multi-Value Parameter Value to a Query

You can bind query parameters to multi-value report parameters (except custom SQL queries created in code and hand-written queries in the Query Builder).

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.

  

  • MS SQL Server

    Split the string that the Join() function returns into an array within the stored procedure.

    SQL

    create procedure [dbo].[GetOrdersByID]
    @data varchar(100)
    as
    select * from Orders
    where OrderID in (select value from STRING_SPLIT(@data,','));
    

    Note

    You can use the STRING_SPLIT function in MSSQL server 2016 or later.

  • Oracle

    The following code demonstrates how to implement an Oracle-specific stored procedure that takes a string of parameters:

    SQL

    create or replace procedure get_movies(titles in varchar, rc out sys_refcursor) as
    begin
    open rc for
    select * from movies
    where title in (
        select regexp_substr(titles, '[^,]+', 1, level) from dual
        connect by regexp_substr(titles, '[^,]+', 1, level) is not null
    );
    end;
    

  • MySQL

    The following code demonstrates how to implement a MySQL-specific stored procedure that takes a string of parameters:

    SQL

    create procedure `get_movies`(in titles varchar(100))
    begin
    select *
    from movies
    where find_in_set(title, titles);
    end;
    

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

  

  The following code demonstrates how to get values from a passed table within a stored procedure with a User Defined Table Type parameter:

  SQL

  create type OrderParameters as table (value int, dt datetime);
  
  create procedure [dbo].[get_orders]
  @args OrderParameters readonly
  as
  select * from Orders
  where OrderId in (select value from @args)
  or OrderDate in (select dt from @args)