# Dynamic Query

Dynamic query allows the creation of SQL queries on the fly, enabling flexibility in constructing complex queries based on changing criteria, user input, or business logic. This can involve adding or removing conditions, altering select columns, or changing table names.

The advantages of dynamic queries include flexibility, as they allow applications to adapt to changing requirements, user preferences, or evolving data conditions. However, they may also introduce risks like SQL injection if not handled securely. Therefore, precautions are essential to ensure the safety and reliability of dynamically generated queries in applications.

{% hint style="info" %}
Dynamic queries assist in resolving functionalities, such as creating a REST service for a UI table. Such a service needs to provide the possibility of using a variable number of options for ordering.

```sql
select * from "orders" 
    where "createdAt" >= '01-01-2020' and "createdAt" < '01-01-2024'
    order by "name" desc, "createdAt" asc, "type" desc
        limit 20 offset 10
```
{% endhint %}

***

### SQL

SQL should be defined and configured at runtime. The best scenario is to use [pdk.core.String](../../../structures/pdk.core#string) flow [local variable](broken-reference).

Placeholders for variables should be enclosed by curly brackets.

<pre class="language-sql"><code class="lang-sql">ordersQuery :: String = 'select * from "orders"' //FLOW local variable

supplierId - input parameter

<strong>1. Add WHERE clause
</strong>dateFrom :: String = '01-01-2020' //FLOW local variable
dateTo :: String = '01-01-2024' //FLOW local variable

ordersQuery = ordersQuery +
    ' where "supplierId" = {supplierId} 
        and "createdAt" >= ' + dateFrom + 
      ' and "createdAt" &#x3C; ' + dateTo

<strong>2. Add ORDER BY 
</strong>nameOrderType :: String = 'desc' //FLOW local variable
createdAtOrderType :: String = 'asc' //FLOW local variable
typeOrderType :: String = 'desc' //FLOW local variable

ordersQuery = ordersQuery +
    ' order by "name" ' + nameOrderType +
    ' order by "createdAt" ' + createdAtOrderType +
    ' order by "type" ' + typeOrderType
    
<strong>3. Add LIMIT and OFFSET
</strong>limitValue :: Integer = 20
offsetValue :: Integer = 10 

ordersQuery = ordersQuery + 
    ' limit ' + limitValue + ' offset ' + offsetValue
</code></pre>

***

### Input Parameters

All input parameters are optional. System maps provided values into placeholders defined in the sql query string.

The parameter name is indicated by the text enclosed within curly brackets { }. The name should consist only of letters, digits, and underscore characters, , and must start with a letter.

<pre class="language-sql"><code class="lang-sql">ordersQuery :: String = 'select * from "orders"' //FLOW local variable

<strong>1. Add WHERE clause
</strong>ordersQuery = ordersQuery +
    ' where "createdAt" >= {dataFrom} and "createdAt" &#x3C; {dataTo}'
    
<strong>dataFrom, dataTo - variables that will be defined during flow execution
</strong></code></pre>

All detailed information on the [Query Input Parameters page](query-input-parameters).
