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

• How to execute a procedure in Postgres?
• How to execute a procedure with an OUT parameter in Postgres?
• How to return a result set from a stored function in Postgres?

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

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.

Category

Parameterized Statements fall into 2 categories:

• Prepared statement for select/insert/update/merge statements that allows only IN parameters
• Callable statement for call/exec/begin statements that allows IN and OUT parameters (ie SQL Procedure execution)

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)