Working with query templates

You can use the data you've collected in an earlier step as an input into a subsequent step.

For example, if you've already identified a subset of customers you're interested in, you might then want to pull in supporting data for those specific customers from other databases.

SQL queries

Here's an example of the Handlebars helpers that will help make this possible:

SELECT  * 
FROM    SomeTable
WHERE
  {{#each .}}
  (FirstName {{#sqleq}}'{{FirstName}}'{{/sqleq}} AND LastName {{#sqleq}}'{{LastName}}'{{/sqleq}}) {{#continue}} OR {{/continue}}
  {{/each}}

Given the following input dataset:

Id	Title	FirstName	LastName
1	Mr	NULL	Smith
2	Miss	Amy	Jones

the following SQL will be generated:

SELECT  * 
FROM    SomeTable
WHERE
  (FirstName IS NULL AND LastName = 'Smith') OR
  (FirstName = 'Amy' AND LastName = 'Jones')

Let's break down that template a little further.

Placeholders

We use the standard Handlebars approach of using double moustaches as placeholders for values: {{FirstName}}.

Values with double moustaches are escaped automatically so that they're safe to use in TSQL. This means an input value of l'amour would be escaped as l''amour , for example.

If you don't want that escaping to happen for some reason, you can use triple moustaches: {{{FirstName}}}.

each - iterating input rows

The {{#each .}} helper allows you to iterate through the rows of your input dataset. The . refers to the rows in the root array of rows. This is a standard Handlebars context.

sqleq - equality and NULLs

The {{#sqleq}} helper is there to help you write WHERE clauses based on the data you've collected so far:

WHERE FirstName {{#sqleq}}'{{FirstName}}'{{/sqleq}}

Notice how there's no = operator in the above and that the quotes for the value are within the helper rather than outside it.

The first row in the above table has a NULL FirstName value. If we'd written the template as follows:

WHERE FirstName = '{{FirstName}}'

this would've resulted in the following SQL:

WHERE FirstName = ''

In databases, an empty string is very different to a NULL value, so this won't work. We need to lose the quotes and use the word NULL. We also need to change the syntax a bit: in TSQL, you don't write FirstName = NULL, it has to be FirstName IS NULL.

When the {{#sqleq}} helper encounters a NULL value, it knows it needs to use the "IS" syntax, and when the value isn't NULL, it knows that it:

1. needs to use an = operator; and

2. needs to apply quotes around the value.

The result is as follows:

WHERE
  (FirstName IS NULL AND LastName = 'Smith') OR
  (FirstName = 'Amy' AND LastName = 'Jones')

continue - line suffixes

When generating SQL from multiple rows, you'll often need to apply a suffix to each clause apart from the final one. You can see this in the example above: the first line of the WHERE clause has an OR suffix, but the final line doesn't.

The {{#continue}} helper allows you to do this with a customisable suffix.

As a further example, here's how you'd use the same dataset to create an IN clause:

SELECT  * 
FROM    SomeTable
WHERE   UserId IN (
  {{#each .}}
  {{Id}}{{#continue}},{{/continue}}
  {{/each}}
)

The above template would result in the following SQL:

SELECT  * 
FROM    SomeTable
WHERE   UserId IN (
  1,
  2
)

Other Helpers

The full Handlebars templating language, its built-in helpers and extension helpers are at your disposal.

Make sure that you create SQL statements that are syntactically valid for the target database platform.

The Handlebars documentation refers to HTML escaping throughout its guidance. This is because the main way in which Handlebars is used is in generating web pages.

However, in our implementation of Handlebars, HTML escaping has been replaced with TSQL escaping as described above.

JSON queries

Documentation needs to be completed!

Here's an example of the Handlebars helpers that will help make this possible:

SELECT  * 
FROM    SomeTable
WHERE
  {{#each .}}
  (FirstName {{#sqleq}}'{{FirstName}}'{{/sqleq}} AND LastName {{#sqleq}}'{{LastName}}'{{/sqleq}}) {{#continue}} OR {{/continue}}
  {{/each}}

Given the following input dataset:

Id	Title	FirstName	LastName
1	Mr	NULL	Smith
2	Miss	Amy	Jones

the following SQL will be generated:

SELECT  * 
FROM    SomeTable
WHERE
  (FirstName IS NULL AND LastName = 'Smith') OR
  (FirstName = 'Amy' AND LastName = 'Jones')

Let's break down that template a little further.

Placeholders

We use the standard Handlebars approach of using double moustaches as placeholders for values: {{FirstName}}.

Values with double moustaches are escaped automatically so that they're safe to use in TSQL. This means an input value of l'amour would be escaped as l''amour , for example.

If you don't want that escaping to happen for some reason, you can use triple moustaches: {{{FirstName}}}.

each - iterating input rows

The {{#each .}} helper allows you to iterate through the rows of your input dataset. The . refers to the rows in the root array of rows. This is a standard Handlebars context.

sqleq - equality and NULLs

The {{#sqleq}} helper is there to help you write WHERE clauses based on the data you've collected so far:

WHERE FirstName {{#sqleq}}'{{FirstName}}'{{/sqleq}}

Notice how there's no = operator in the above and that the quotes for the value are within the helper rather than outside it.

The first row in the above table has a NULL FirstName value. If we'd written the template as follows:

WHERE FirstName = '{{FirstName}}'

this would've resulted in the following SQL:

WHERE FirstName = ''

In databases, an empty string is very different to a NULL value, so this won't work. We need to lose the quotes and use the word NULL. We also need to change the syntax a bit: in TSQL, you don't write FirstName = NULL, it has to be FirstName IS NULL.

When the {{#sqleq}} helper encounters a NULL value, it knows it needs to use the "IS" syntax, and when the value isn't NULL, it knows that it:

1. needs to use an = operator; and

2. needs to apply quotes around the value.

The result is as follows:

WHERE
  (FirstName IS NULL AND LastName = 'Smith') OR
  (FirstName = 'Amy' AND LastName = 'Jones')

continue - line suffixes

When generating SQL from multiple rows, you'll often need to apply a suffix to each clause apart from the final one. You can see this in the example above: the first line of the WHERE clause has an OR suffix, but the final line doesn't.

The {{#continue}} helper allows you to do this with a customisable suffix.

As a further example, here's how you'd use the same dataset to create an IN clause:

SELECT  * 
FROM    SomeTable
WHERE   UserId IN (
  {{#each .}}
  {{Id}}{{#continue}},{{/continue}}
  {{/each}}
)

The above template would result in the following SQL:

SELECT  * 
FROM    SomeTable
WHERE   UserId IN (
  1,
  2
)

Other Helpers

The full Handlebars templating language, its built-in helpers and extension helpers are at your disposal.

Make sure that you create SQL statements that are syntactically valid for the target database platform.

The Handlebars documentation refers to HTML escaping throughout its guidance. This is because the main way in which Handlebars is used is in generating web pages.

However, in our implementation of Handlebars, HTML escaping has been replaced with TSQL escaping as described above.