Parameterized SQL Queries: /Documentation/Archive/21.7

Parameterized SQL Queries

LabKey Server lets you add parameters to your SQL queries, using the PARAMETERS keyword. When a user views the query, they will first assign values to the parameters (seeing any default you provide) and then execute the query using those values.

Example Parameterized SQL Query

The following SQL query defines two parameters, MinTemp and MinWeight:

PARAMETERS
(
    MinTemp DECIMAL DEFAULT 37,
    MinWeight DECIMAL DEFAULT 90
)


SELECT PhysicalExam.ParticipantId,
PhysicalExam.date,
PhysicalExam.weight_kg,
PhysicalExam.temperature_C
FROM PhysicalExam
WHERE temperature_C >= MinTemp and weight_kg >= MinWeight

By default, parameterized queries are hidden in the Schema Browser. Select Show Hidden Schemas and Queries to view. Go to the Schema Browser, and look in the far lower left. For details, see SQL Query Browser.

Note: Only constants are supported for default parameter values.

Example Using the URL to Pass Parameter Values

To pass a parameter value to the query on the URL use the syntax:

&query.param.PARAMETER_NAME=VALUE

For example, the following URL sets the parameter '_testid' to 'Iron'. Click below to execute the query.

https://training-01.labkey.com/Demos/SQL%20Examples/query-executeQuery.view?schemaName=lists&query.queryName=PARAM_Zika23Chemistry&query.param._testid=Iron

Example API Call to the Parametrized Query

You can pass in parameter values via the JavaScript API, as shown below:

<div id="div1"></div>
<script type="text/javascript">

function init() {

    var qwp1 = new LABKEY.QueryWebPart({
      renderTo: 'div1',
      title: "Parameterized Query Example",
      schemaName: 'study',
      queryName: 'ParameterizedQuery',
      parameters: {'MinTemp': '36', 'MinWeight': '90'}
    });
}
</script>

The parameters are written into the request URL as follows:

query.param.MinTemp=36&query.param.MinWeight=90

User Interface for Parameterized SQL Queries

You can also pass in values using a built-in user interface. When you view a parameterized query in LabKey Server, a form is automatically generated, where you can enter values for each parameter.

Go to the Schema Browser: (Admin) > Go To Module > Query.
On the lower left corner, select Show Hidden Schemas and Queries. (Parameterized queries are hidden by default.)
Locate and select the parameterized query.
Click View Data.
You will be presented with a form, where you can enter values for the parameters:

ETLs and Parameterized SQL Queries

You can also use parameterized SQL queries as the source queries for ETLs. Pass parameter values from the ETL into the source query from inside the ETL's config XML file. For details see ETL: Examples.

LabKey Support

LabKey Support