SqlRender (version 1.6.2)

render: 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

Value

A character string containing the rendered SQL.

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.

Examples

# NOT RUN {
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")
# }