In this article, you will learn how to send parameters to Query and Compare Query, in exMon. If you need to, for example, filter results by some external value you can use Query Parameters.

Designing Queries with parameters

When designing your query parameters, you need to follow this format: {@parameternameedefault value]}

• @parametername represents the name of the parameter you want to query.
• gdefault value] is only used when testing the query in exMon Data Governance.
• You can add multiple parameters or reuse the same parameter.

In the example below, we select products by colour and price range. Specifically, we are using a test query in exMon Data Governance to 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”. You can do this 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 the object group to run with different parameters.

Parameter List

Parameter lists are a great way to limit what parameter values are available to the exMon end users. 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:

{@parameternameadefault: value]apreview:value2]vdatatype:list]tlist_items: value1
|’Display Value1’, value2|’Display Value2’, value3|’Display Value3’]}

List_items contain 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:

{@parameternamerdefault: value]epreview:value2]:datatype:list]wlist_function: 
client.get_all_users] list_function_key:user_id]slist_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 is the value of the display shown in exMon Portal.

An example of a function that exMon could use:

CREATE FUNCTION eclient].Rget_all_users]

(

    @UserId int, @SystemId int

)

RETURNS TABLE AS RETURN

(

    SELECT user_id, user_name, full_name, email
    FROM c_user

)