render {SqlRender}

Render SQL code based on parameterized SQL and parameter values

Description

render Renders SQL code based on parameterized SQL and parameter values.

Usage

render(sql, warnOnMissingParameters = TRUE, ...)

Arguments

sql

The parameterized SQL

warnOnMissingParameters

Should a warning be raised when parameters provided to this function do not appear in the parameterized SQL that is being rendered? By default, this is TRUE.

...

Parameter values

Details

This function takes parameterized SQL and a list of parameter values and renders the SQL that can be send to the server. Parameterization syntax:

@parameterName

Parameters are indicated using a @ prefix, and are replaced with the actual values provided in the render call.

{DEFAULT @parameterName = parameterValue}

Default values for parameters can be defined using curly and the DEFAULT keyword.

{if}?{then}:{else}

The if-then-else pattern is used to turn on or off blocks of SQL code.

Value

A character string containing the rendered SQL.

Examples

render("SELECT * FROM @a;", a = "myTable")
render("SELECT * FROM @a {@b}?{WHERE x = 1};", a = "myTable", b = "true")
render("SELECT * FROM @a {@b == ''}?{WHERE x = 1}:{ORDER BY x};", a = "myTable", b = "true")
render("SELECT * FROM @a {@b != ''}?{WHERE @b = 1};", a = "myTable", b = "y")
render("SELECT * FROM @a {1 IN (@c)}?{WHERE @b = 1};",
  a = "myTable",
  b = "y",
  c = c(1, 2, 3, 4)
)
render("{DEFAULT @b = \"someField\"}SELECT * FROM @a {@b != ''}?{WHERE @b = 1};",
  a = "myTable"
)
render("SELECT * FROM @a {@a == 'myTable' & @b != 'x'}?{WHERE @b = 1};",
  a = "myTable",
  b = "y"
)
render(
  sql = "SELECT * FROM @a;",
  warnOnMissingParameters = FALSE,
  a = "myTable",
  b = "missingParameter"
)