Parameterized Queries
  • 02 Aug 2022
  • 1 Minute to read
  • Dark
    Light

Parameterized Queries

  • Dark
    Light

Overview

Parameterized Queries are used to prevent SQL Injections. In these types of queries, parameters that are defined become variables when the query is run in the workflow process. Parameterized queries can then be used as steps in a flow. These steps can be found under Integration > Database > [DatabaseName] as one of the many query steps for the category.


Example

This example demonstrates creating and using a Parameterized Query to get a list of currently active accounts. The example will pull the accounts from the dbo.entity_account table. The default Decisions database or another database can be integrated. See Integrating an External Database with a Flow for more information on integrating a database.

  1. In the Designer Project, click on CREATE DATATYPES/INTEGRATIONS on the Global Action Bar and select Database Integration. Click Add Query.
  2. Select an existing connection in the Database Integration dialog window and click NEXT.
  3. Under the QUERY SETTINGS catalog, provide a name for the query (GetAccountsByCompany). In the Query text area, enter "select * FROM dbo.entity_account WHERE is_active=@is_active". Under the INPUT PARAMETERS category, click the pencil icon to open the Edit Parameters dialog window.

    Postgress integrations will use a '?' instead of '@' for indicating a parameter. Variables will then be filled out in the order of the parameters. For Example: 'select * from ? where entity_name = ?'. There will be two parameters; the first will be inserted after 'from' and the second after '='.

  4. Enter "1" in the Default Value text box, and change the Type selected as Int32. Then click OK. 


  5. Click RUN QUERY to view the results.
  6. Click OK to save the query.

Was this article helpful?