Pass Query Parameters

• Jun 02, 2020
• 4 minutes to read

A query parameter holds an external value that is inserted into an SQL statement before query execution. An associated expression can dynamically generate this value or it can be static.

The query parameter value is inserted into the resulting SQL query string in the @QueryParameterName placeholder’s position.

In dashboards, query parameters are used in the following scenarios:

• When filtering dashboard data at the data source level using the Query Builder.

• When binding a dashboard to a stored procedure an SQL data source provides.

You can access query parameters using the Parameters property of the query the dashboard’s SqlDataSource component exposes. These parameters include the ones you created within the Query Builder or that were generated for the data source’s stored procedure.

• Configure Query Parameters
• Provide the Query Parameter Value
• Pass a Multi-Value Parameter Value to a Query
• Pass a Multi-Value Dashboard Parameter Value to a Stored Procedure

Configure Query Parameters

The Query Editor dialog contains added query parameters’ settings.

The following properties are available for each query parameter:

Name	Specifies a parameter’s name.
Type	Specifies the parameter’s type.
Expression	Specifies whether an expression is used to specify a parameter’s value.
Value	Specifies the parameter’s value. If the Expression check box is enabled, you can invoke the Expression Editor dialog to specify the expression or select an existing dashboard parameter to pass to the SQL query.

Use the Add / Remove buttons to add or remove query parameters.

Click the Preview… button if you need to preview the query result. Click Finish to complete the query modification.

Provide the Query Parameter Value

Below, you can see how to specify a value for a query parameter.

Specifying a static value

Choose a query parameter’s value type and set a static value to the Value property according to the selected type.

Providing a dynamic value

Create a complex expression used to calculate the parameter’s actual value dynamically. Change Type to Expression and click the Value property’s ellipsis button.

Construct an expression in the invoked Expression Editor.

Pass a Multi-Value Parameter Value to a Query

You can map multi-value parameters to query parameters. For instance, the following query selects the orders whose IDs can be found within the values the @OrderID query parameter provides.

Pass a Multi-Value Dashboard Parameter Value to a Stored Procedure

You cannot pass a multi-value parameter value to a stored procedure directly. Use the Join() expression function in the Expression Editor to convert the array of parameter values to a string if you use MS SQL Server, MySQL or Oracle database systems.

Below are examples of how to get parameter values from the passed string within a stored procedure.

MS SQL Server

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

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

Note that you can use the (STRING_SPLIT)[https://docs.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql] function in MSSQL server 2016 and later.

You can also implement a stored procedure with a User Defined Table Type parameter. In this case, use the CreateTable() expression function to create a table from values of several multi-value parameters.

The following code demonstrates how to implement a stored procedure that gets values from the table the CreateTable() function produces:

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)

Oracle

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

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:

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