Parameterized SQL Queries: /Documentation/Archive/18.1

Parameterized SQL Queries

Documentation: Version 18.1

Parameterized SQL Queries

LabKey Server lets you add parameters to your SQL queries, using the PARAMETERS keyword.

Example Parameterized SQL Query

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

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

SELECT "Physical Exam".ParticipantId,
"Physical Exam".date,
"Physical Exam".Weight_kg,
"Physical Exam".Temp_C
FROM "Physical Exam"
WHERE Temp_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.

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) > Developer Links > Schema Browser.
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.