Pass Query Parameters
- 4 minutes to read
A query parameter holds an external value that is inserted into an SQL statement before query execution. This value can be static or dynamically generated by an associated expression.
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 on 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 the 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 required 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 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
Enable the Expression checkbox for a parameter.
The following options are used to calculate the parameter’s actual value dynamically:
Create a complex expression by expanding the Value property’s drop-down list and selecting Expression Editor.
Construct an expression in the invoked Expression Editor.
Map a new dashboard parameter to a query parameter by expanding the Value property’s drop-down list and selecting Add Dashboard Parameter.
Specify dashboard parameter settings in the invoked Dashboard Parameter dialog. Specify the dashboard parameter type according to the type of the corresponding query parameter. Click OK to exit the dialog.
Map a dashboard parameter that already exists in a dashboard to a query parameter by expanding the Value property’s drop-down list and selecting the parameter you want to use. An appropriate expression string is generated automatically.
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.
The parameter values are 10248, 10249, and 10250. The image below show the preview result:
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)[https://docs.microsoft.com/en-us/sql/relational-databases/tables/use-table-valued-parameters-database-engine] parameter. In this instance, use the CreateTable() expression function to prepare 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;