Use Query Parameters

• Mar 20, 2023
• 11 minutes to read

This topic describes the common tasks that you can do with query parameters.

Query Parameters Overview

A SQL query can include query parameters. A query parameter holds a value that is inserted into a SQL statement before the query is executed. The query parameter value can be static, if specified explicitly, or dynamic, when an expression is evaluated to obtain a value.

In a query string, the parameter name is preceded by the \@ symbol (Microsoft SQL Server). The following SQL string includes the pOrderId parameter:

select [OrderDetailsExtended].*
  from [OrderDetailsExtended]
 where [OrderDetailsExtended].[OrderID] = @pOrderId
order by [OrderDetailsExtended].[ExtendedPrice]
       desc

A report retrieves data from a SQL database with the SqlDataSource object. The SqlDataSource class exposes the SqlDataSource.Queries property that returns a collection of SqlQuery objects. SqlQuery is the base class for different query types, so the Queries collection may contain the following query types:

SelectQuery

A query that can be visually constructed in the Query Builder.

CustomSqlQuery

A query created from a SQL string.

StoredProcQuery

A query that uses a stored procedure to retrieve data.

The base SqlQuery.Parameters property contains a collection of query parameters.

Use Report Parameter to Filter Report Data at the Data Source Level

In this scenario, a report parameter is bound to the query parameter to filter the data supplied to the report. For more information on report parameters, review the following help topic: Use Report Parameters.

Start with a report that is bound to the SQL database and already has a report parameter. Open the report in the Report Designer.

Do the following to add a parameter to the SQL query and bind the query parameter to the report parameter:

1. Invoke the Field List or the Report Explorer window, and right-click the data source item. Select Manage Queries in the context menu to invoke the Query Builder.

   

   For more information on how to create a report bound to a SQL database, review the following help topics:

   • Create a Query or Select a Stored Procedure
   • Bind a Report to a Database

2. In the Query Builder window, click Edit Parameters to invoke the Query Parameters dialog.

   

3. Click Add to add a new query parameter. The default parameter name is Parameter1.

   

   In this dialog, you can specify the following parameter settings:

   Name

   Specifies the parameter name.

   Type

   Specifies the parameter type.

   Value

   Specifies the parameter value.

   If you enable the Expression option, the Value property editor displays a drop-down list with the following options:

   • Expression Editor

     Invokes the Expression Editor dialog that helps you construct an expression.

     

     An expression can include report parameters, as shown in the image above.

   • New Report Parameter

     Invokes the Parameter property window that allows you to create and configure a new report parameter, and bind it to the query parameter.

     

   • ?ReportParameterName

     Report parameters are displayed in the drop-down list. Select the parameter name in the list to bind the report parameter to the current query parameter.

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

   

5. Enter the filter expression:

   

6. Click OK. The filter expression is added as a SQL statement’s WHERE clause:

   select [OrderDetailsExtended].*
   from [OrderDetailsExtended]
   where
       ([OrderDetailsExtended].[OrderID] = @Parameter1)
   order by [OrderDetailsExtended].[ExtendedPrice]
       desc
   

Bind a Report Parameter to a Query Parameter in Code

In this section, you will find the code that creates a query parameter and binds it to a report parameter.

Use SelectQuery

SelectQuery is a query that can be visually constructed in the Query Builder. Add a new query parameter and include the report parameter name in an expression assigned to the FilterString property.

C#

using DevExpress.DataAccess.Sql;
// ...
QueryParameter parameter = new QueryParameter() {  
    Name = "Parameter1",  
    Type = typeof(DevExpress.DataAccess.Expression),  
    Value = new DevExpress.DataAccess.Expression("?pOrderId", typeof(int))
};  
selectQuery.Parameters.Add(parameter);  
selectQuery.FilterString = "[OrderDetailsExtended.OrderID] = ?Parameter1";

VB.NET

Imports DevExpress.DataAccess.Sql
' ...
Private parameter As New QueryParameter() With {
    .Name = "Parameter1",
    .Type = GetType(DevExpress.DataAccess.Expression),
    .Value = New DevExpress.DataAccess.Expression("?pOrderId", GetType(Integer))
}
selectQuery.Parameters.Add(parameter)
selectQuery.FilterString = "[OrderDetailsExtended.OrderID] = ?Parameter1"

Use CustomSqlQuery

CustomSqlQuery is a query created manually, from a SQL string. Add a new query parameter and append a filter string to the Sql property. The filter string uses a report parameter to specify the filter criteria.

C#

using DevExpress.DataAccess.Sql;
//...
CustomSqlQuery query = new CustomSqlQuery();  
query.Name = "Order Details Extended";

QueryParameter parameter = new QueryParameter() {  
    Name = "Parameter1",  
    Type = typeof(DevExpress.DataAccess.Expression),  
    Value = new DevExpress.DataAccess.Expression("?pOrderId", typeof(string))
}; 

query.Parameters.Add(queryParameter1);
query.Sql += " WHERE \"Order Details Extended\".\"OrderID\" = ?Parameter1";  
sqlDataSource1.Queries.Add(query);

VB.NET

Imports DevExpress.DataAccess.Sql
'...
Private query As New CustomSqlQuery()
query.Name = "Order Details Extended"
queryParameter1 = New DevExpress.DataAccess.Sql.QueryParameter()
queryParameter1.Name = "Parameter1"
queryParameter1.Type = GetType(DevExpress.DataAccess.Expression)
queryParameter1.Value = New DevExpress.DataAccess.Expression("JOIN(?pOrderIds)", GetType(String))
query.Parameters.Add(queryParameter1)
query.Sql &= " WHERE ""Order Details Extended"".""OrderID"" IN (SELECT value from STRING_SPLIT(@Parameter1,','))"
sqlDataSource1.Queries.Add(query)

Stored Procedure Query

Review the following help topic for a code example: StoredProcQuery.

Bind a Multi-Value Report Parameter to a Query Parameter

A multi-value parameter allows a user to select multiple values and apply them to filter report data.

To use a multi-value report parameter in a query created with the Query Builder UI (the SelectQuery object), bind the query parameter to the report parameter and specify the InOperator in the filter expression. For more information on report parameters and filter operators, review the following help topics:

• Multi-Value Report Parameters
• Advanced Filtering, Custom Filtering, Search

If you create a query from text (that is, enter the query string manually), the resulting query is a CustomSqlQuery object. It cannot process the multi-value report parameter, and you should convert the report parameter into a string, and bind the resulting string to a query parameter. The query string should use a function that translates the passed query parameter into an array of values suitable for a SQL expression. For Microsoft SQL Server 2016 and later, you can use the STRING_SPLIT function.

Note

The query that supplies a list of values to the report parameter (the dynamic lookup list) should be different from the query that supplies data to the report.

Review the following sections for step-by-step guides to two alternative methods that create a parameterized query bound to a multi-value parameter.

Method 1 - Create a Query with the Query Builder UI

To create a multi-value parameter and bind it to the query, do the following:

1. Create a new query used to populate the list of selectable values for a report parameter. To do this, open the Field List window, right-click the data source item, and select Manage Queries in the context menu to invoke the Query Builder. Add a new query. You can also click the Clone button to clone the existing query:

   

   The cloned query name has an underscore and a sequential number added to the original query name.

2. Click Filter to invoke the Filter Editor:

   

3. Enter the filter expression. A new multi-value parameter is created in the interim:

   

4. The resulting filter string is shown in the following image:

   

   Click OK to close the window and save the filter.

5. The Query Builder displays the text of the parameterized query:

   

6. You can view and edit the newly created report parameter (specify the sort order) in the Report Parameters Editor window:

   

Method 2 - Create a Query From the Query String

To create a multi-value parameter and bind it to a custom query (a query that you typed manually), do the following:

1. Create a new query used to populate the list of selectable values for a report parameter. To do this, open the Field List window, right-click the data source item, and select Manage Queries in the context menu to invoke the Query Builder. Add a new query. As an alternative, you can click the Clone button to clone the existing query:

   

   Note that Edit SQL in the top right window corner is enabled. This means that when you edit the CustomSqlQuery object, you can edit the query manually in the text editor.

2. Add a new multi-value parameter to the report. For more information, review the following help topic: Multi-Value Report Parameters. The image below shows the Report Parameters Editor window for a multi-value report parameter that retrieves its values from a query created in the previous step.

   

3. Open the Field List window, right-click the data source item, and select Manage Queries in the context menu to invoke the Query Builder window. Click Edit Parameters to invoke the Query Parameters dialog. Click Add to add a new query parameter. The default parameter name is Parameter1. Enable the Expression option and select Expression Editor in the Value drop-down list. Enter the following expression in the Expression Editor window:

   JOIN(?pOrderIds)
   

   

   Click OK to save the parameter and close the window.

4. Edit the query and use the STRING_SPLIT function (available with Microsoft SQL Server 2016 and later) to convert the query parameter into an array of values:

   

   select "Order Details Extended"."OrderID",
       "Order Details Extended"."ProductID",
       "Order Details Extended"."ProductName",
       "Order Details Extended"."UnitPrice",
       "Order Details Extended"."Quantity",
       "Order Details Extended"."Discount",
       "Order Details Extended"."ExtendedPrice",
   cast (("Order Details Extended"."Quantity" *
       "Order Details Extended"."UnitPrice") as double
       precision) as "SubTotal"
   from "dbo"."Order Details Extended"
       "Order Details Extended"
   where "Order Details Extended"."OrderID" 
   in (select value from STRING_SPLIT(@Parameter1,','))
   

Pass Report Parameters to 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.

For more information, review the following help topic: Bind a Report to a Stored Procedure.

Use the query’s Parameters property to access the query parameters collection. The collection includes 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.

Pass a Multi-Value Report Parameter to a Stored Procedure

You cannot pass a multi-value parameter value directly to a stored procedure. You must use Functions for Stored Procedures to combine parameter values into a string or table to pass them to a procedure, and retrieve them as described below.

Use the Join() Expression Function

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

Microsoft 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 Microsoft SQL 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;

IBM DB2

The following code is the stored procedure for IBM DB2 database that takes a string of parameters:

CREATE OR REPLACE PROCEDURE "GetOrdersByID" (data varchar(100)) READS SQL DATA DYNAMIC RESULT SETS 1  
    BEGIN
        DECLARE cursor_1 CURSOR WITH RETURN FOR
        SELECT *
        FROM "Orders"
        WHERE "OrderID" in (SELECT * from XMLTABLE('$d/r/i' PASSING XMLPARSE(DOCUMENT CAST('<r><i>' || replace(data , ',' , '</i><i>') || '</i></r>' as CLOB) ) as "d" COLUMNS ITEM VARCHAR(255) PATH '.'));
        OPEN cursor_1;
    END

Use the CreateTable() Expression Function

Use the CreateTable() expression function to generate a table populated with 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)