Sql Parameterized Statement

About

A Parameterized Statement is a SQL statement templated with arguments called parameters

They are runtime where

the parameters are the arguments
the placeholder (? or $N) is where the parameter value is applied

Howto

Example

Select

An analytics SQL Select statement with 1 placeholder (ie 1 parameter)

select d_week_seq1
       ,round(sun_sales1/sun_sales2,2)
       ,round(mon_sales1/mon_sales2,2)
where
  d_year = ?
group by
  d_week_seq1

Insert

An DML insert statement with 2 placeholders (ie 2 parameters)

INSERT INTO products (name, price) VALUES (?, ?);

Stored Procedure

An stored procedure statement with 2 placeholders (ie 2 parameters)

Example

with the Postgres call

? = call upper(?)

for all database systems with jdbc escaping

{ ? = call upper(?) }

Parameters

SQL Parameters are a list of parameter that can be used to parametrized DML statements.

In a command comparison, they are the arguments of a SQL Statement

Parameters are also known as bind variables

Attribute

You can add to a SQL request, the parameters attributes

Example of SQL Request manifest

kind: sql-request
spec:
  data-uri: (script.sql@cd)@database
  data-def:
    parameters:
        # the value (default: null)
      - value: value1
        # The direction: IN (default), OUT, INOUT
        direction: IN
        # the data type (mandatory when the value is null)
        type: varchar
        # the column name if the direction is OUT, INOUT (default: para_N)
        name: para_1
      - ...

where:

value is the value of a parameter. If omitted, the value is null.
type is the data type of the value.
- Mandatory if the value is not set
- Derived from the Yaml data type of the value if not set
direction is the direction of the parameter. Default to IN
name is the column name used in the result data resource when a direction is OUT or INOUT

Direction

The direction of a parameter defines if a value should be:

passed to the statement
returned after execution

Direction	Statement Kind	Value passed to the statement	Value returned after execution
IN (default)	All DML	True	False
OUT	Procedure only	False	True
INOUT	Procedure only	True	True

If one of the parameters has a OUT or INOUT direction, they are returned in a data resource.

Placeholder

The placeholder parameter syntax differs depending on your database.

Notation	Database
?	MySQL, SQL Server, Oracle, SQLite, …
$N	PostgreSQL (if not escaped)

Limitations

Table names and column names cannot be parameterized, only value (ie you can only parameterize values, not structural elements)

SQL Subset Support

Not all kind of statement can be written as prepared statement.

You need to refer to your database documentation but generally, they are supported with DML statement (Select, Insert, Update, Merge, Call)