f you need to send parameters to queries, for example to filter results by some external value, you can use Query Parameters.

 

Designing Queries with parameters

Query parameters are added to queries on the format: {@parametername[default value]}.

The default value is only used when testing the query in exMon Administrator.

You can add multiple parameters and reuse the same parameter.

In the example below we select products by color and price range and when testing it in exMon Administrator we select black products that cost between 0 and 200.


If you are using query parameters with tests that use exception management, it is important to deselect “Automatically close exceptions when they are not found in query results” by going into Advanced in Exception Management Properties.

 

Running Queries with parameters on the Portal

When executing the test from the portal, the user will be asked to fill in query parameters.

 

Running Queries with parameters with epcmd.exe

Queries that define parameters must be executed with parameters in the command line.

For example to execute the query above:

epcmd --query 1 --@color "Black" --@pricefrom 0 --@priceto 200

 

Adding to Object Groups

You can add the same query multiple times into object group to run with different parameters.


Parameter List

Parameter lists are a great way to limit what parameter values are available to exMon end user. There are two types of parameter lists, static lists and dynamic lists.

Static List

A static list has a set number of parameter values available and it requires an exMon developer to change available values.

Static list format:{@parametername[default: value][preview:value2][datatype:list][list_items: value1|’Display Value1’, value2|’Display Value2’, value3|’Display Value3’]}

List_items contains a combination of key|display pairs, separated by commas. The key is the value that will be used by executions in exMon but the display value will be shown in exMon Portal.

Dynamic List

A dynamic list requires a reference to a T-SQL function that receives two integer parameters, @UserId and @SystemId and returns available parameter values.

Dynamic list format: {@parametername[default: value][preview:value2][datatype:list][list_function: client.get_all_users][list_function_key:user_id][list_function_display:user_name]}

List_function is the T-SQL function reference, list_function_key is the column name of the key used by executions in exMon and list_function_display the value of the display shown in exMon Portal.

An example of a function that exMon could use:

CREATE FUNCTION [client].[get_all_users]
(
  @UserId int,
  @SystemId int
)
RETURNS TABLE
AS
RETURN
(
  SELECT user_id, user_name, full_name, email
  from c_user
)