Что такое шаблоны в sql

SQL Query Templates

Query Templates allow you to define reusable snippets of SQL components to be used across different reports. This is useful when you have a snippet of code that gets repeated on multiple reports.

A query template is defined by 3 components:

• name : name of template to be used in SQL reports
• variables : dynamic parameters inside the template
• body : content of the template

Example — Abstracting repeated components of your SQL#

Say you have a report that lists all jobs, together with their statuses (stored as int in the database).

You can see that the CASE WHEN J.status is something that will appear again in other reports with Status column. So we can define a << @job_status(status) >> query template as follows:

Then rewrite your report's SQL as:

Now, whenever your report runs, the query template will automatically be replace with the actual template's content. This is just like programming language's functions inside SQL itself.

Adding variables to Query Templates#

Using variables inside query templates allows you to accept additional parameters, when your query template runs inside a report.

The basic query template with variable structure is template(variable1, variable2) . In this example, the query template age_groups accepts two variables that were added, field and range . So this query template with variable would operate as age_groups(field, range) when called, and pass the respective input values into the query template.

Here you can see it in action in a report. In this report, the field variable has accepted a line of SQL code EXTRACT(year from age. ) , while range accepted a shared filter input called age_groups that was added to the report.

ExPEditor

В SSMS существует множество подготовленных шаблонов для разработки и администрирования баз данных SQL Server‘а, шаблоны для Analysis Services и шаблоны для SQL Server Compact Edition. Все эти шаблоны доступны в Template Explorer, для отображения которого необходимо выбрать пункт меню View>Template Explorer.

Шаблоны организованы в виде дерева и сгруппированы по типам объектов в папки. В панели инструментов Template Explorer‘а можно выбрать тип шаблонов, а в нижней его части отображается список последних использованных шаблонов, что также облегчает работу, если постоянно используются только пара-тройка шаблонов. Для увеличения области Recently Used Templates, просто переместите сепаратор вверх.

Template Explorer для шаблонов SQL Server

Данные, заключенные в угловые скобки и разделенные запятыми, будут использованы в диалоге спецификации значений параметров шаблона (Specify Values for Template Parameters), для вызова которого, выберите пункт меню Query>Specify Values for Template Parameters. или нажмите сочетание клавиш Ctrl+Shift+M.

How to create and customize SQL Server Templates

SQL Server Templates are SQL scripts, containing SQL code, frequently used by developers and DBAs in their daily work (e.g. Create Table, Add Column, Drop Column, Create Database, Create Logins etc.):

SQL Server Templates exists since SQL Server 2000, but the Template Explorer feature SQL Server Templates was introduced in SQL Server 2005. The Template Explorer is a collection of folders that contains the templates based on category (e.g. Database, Backup, Function):

In SQL Server 2000 the file extension for SQL templates was .tsql. Since SQL Server 2005 this extension is changed to .sql.

From SQL Server 2012 the title for the pane that contains SQL Server Templates is changed from the Template Explorer to the Template Browser:

By default, the Template Browser pane does not open when SQL Server Management Studio (SSMS) starts. To initiate the Template Browser pane, go to the SSMS main menu, select the View menu and from the list, choose the Template Explorer option or use a combination of keyboard shortcut Ctrl+Alt+T:

This will open the Template Browser pane, by default the Template Browser pane will be shown on the right side of SSMS:

There are three ways to insert SQL template in the query window. Double clicking on it or right click on the SQL template and from the popup menu choose the Open option:

Will open SQL template in the new query window:

And the third is a drag and drop way, will open SQL template in a targeted query window:

Replace Template parameters with values

The SQL template may or may not include parameterization. SQL template parameters are placeholders for the values that need to be changed by the user. A SQL template parameter starts with less-than sign “<” and ends with a greater than sign “>”. There are three parts of the SQL template parameter between less-than and greater-than sign:

Parameter name – the name of the parameter that need to be changed (e.g. schema_name, database_name, table_name)

Data type – the data type of the parameter (e.g. int, varchar, date, sysname)

Value – parameter shows what value will be used as a default for each parameter

The Data type and Value are optional and can be omitted from the parameter list. Note, when omitted the comma, which separates them must remain:

Specify values for template parameters

In order to replace parameters in SQL template with corresponding values, open the SQL template that want to be use from the Template Browser pane (e.g. Create Database):

From the SSMS main menu, choose the Query menu and from the list, select the Specify Values for Template Parameters option:

Or use the keyboard shortcut Ctrl+Shift+M. In both ways will open the Specify Values for Template Parameters window for entering a values:

In the Value column, replace the default value with the name of the database that will be created (e.g. MyDatabase) and click the OK button:

The SQL template will remove everything between the <>, and replace with the name of the database that inserted in the Specify Values for Template Parameters window:

When this template is executed, a database will be created with the name “MyDatabase”:

Creating a custom SQL template

To create a SQL template, navigate to the desired folder in the Template Browser or create a new folder in the Template Browser. Right click on the folder and from the popup menu choose the New and select the Folder command:

The New Folder will appear in the bottom of the Template Browser and stays down after renaming e.g. Custom SQL Templates:

In order for the newly created folder to sorted in alphabetical order in the Template Browser SSMS must be restarted:

Right click on the newly created folder and from the popup menu, choose the New option, and then click the Template command, type the name for a new SQL template e.g. PersonInfo:

Right click on the newly created SQL templates and press the Edit command, this will open SQL template in a new blank query window:

Place the SQL code:

Change the SQL variable items to parameters e.g. ken0@ to <Address_Name,varchar(100),ken0@> and 6 replace with <Phone_Number,varchar(100),6> :

…and save changes.

When the PersonInfo template is double-clicked on, will open a new query window with the code in it:

Use the shortcut Ctrl+Shift+M to open the Specify Values for Template Parameters dialog box and change values in the Value field with appropriate one and press the OK button:

This will change parameters with the corresponding values:

Open vs Edit SQL template

When opening the template via double-clicking or via the Open command from the popup menu, a new query will open and populate the query with the contents of the template file. Making changes to that query will not affect the existing template. But, if the Edit command is used to make changes, any changes that were made will be saved for future use.

Location of SQL templates

Depending on the version of SQL Server default (built-in) SQL templates are located under this path:

The first time when the Template Browser pane is initiated, a copy of the SQL templates will be placed under the user’s folder in this path:

Custom (user defined) SQL templates will also be placed in this location.

If SQL Server isn’t installed on the C drive, replace C with the name of the drive on which is installed SQL Server

New in SSMS 2016

When initiating the Specify Values for Template Parameters the parameters that need to be changed will be highlighted in the query window:

Name already in use

If nothing happens, download GitHub Desktop and try again.

Launching GitHub Desktop

If nothing happens, download GitHub Desktop and try again.

Launching Xcode

If nothing happens, download Xcode and try again.

Launching Visual Studio Code

Your codespace will open once ready.

There was a problem preparing your codespace, please try again.

Latest commit

Git stats

Files

Failed to load latest commit information.

README.md

SQL template engine (SQL шаблонизатор)

SQL шаблонизатор — это инструмент, облегчающий конструирование SQL запросов. В SQL шаблон вместо меток-заменителей (placeholders) подставляются настоящие данные (строки, числа, булевы, null, массивы, объекты). Важно, что при подстановке данных в шаблон они квотируются, чтобы недопустить SQL инъекций. Далее готовый SQL запрос уже можно выполнить в СУБД (PostgreSQL, MySQL, ClickHouse).

Основное преимущество этого шаблонизатора — это формирование сложных SQL запросов в читабельном виде.

Этот SQL шаблонизатор может применяться вместо ORM и Query Builder. См. таблицу сравнения.

Он работает очень быстро, т.к. регулярные выражения для синтаксического анализа (парсинга) SQL почти не используются.

Шаблонизатор представляет собой один PHP файл SqlTemplate.php , в котором всего 2 публичных метода.

Замена именованных меток-заменителей

Синтаксис меток-заменителей в терминах регулярных выражений PCRE:

:value , где value — это произвольное название. Квотирует значение, массив возвращается в синтаксисе <…>. Возможные типы значений: string , integer , float , boolean , null , array , SqlExpression , \DateTime .

@field , где field — это произвольное название. Квотирует идентификатор объекта БД (название таблицы, колонки, процедуры и т.п.). Возможные типы значений: string , array , SqlExpression .

:values[] На входе ожидает массив и квотирует каждый его элемент как значение и склеивает их через запятую. Если передан НЕ массив, то работает аналогично метке-заменителю :value . Удобно использовать следующих конструкциях, примеры: IN(:values[]) , VALUES(:values[]) , COALESCE(:values[]) , ROW(:values[]) , ARRAY[:values[]]

@fields[] На входе ожидает массив и квотирует каждый его элемент как идентификатор объекта БД (название таблицы, колонки, процедуры и т.п.) и склеивает их через запятую. Если передан НЕ массив, то работает аналогично метке-заменителю @field . Удобно использовать следующих конструкциях, примеры: INSERT INTO t (@fields[]) … , COALESCE(@fields[]) , ROW(@fields[]) , ARRAY[@fields[]]

?field , где field — это произвольное название. Эта метка-заменитель называется условной, т.к. используется только внури условных блоков (может стоять там в любой позиции). На входе ожидает логическое значение. Нелогическое значение приводит к логическому по правилам PHP ( if ($value) . ). Если true , то условный блок, в котором находится эта метка-заменитель, и вложенные блоки будут сохранены, а сама метка-заменитель будет удалена. Если false , то условный блок, в котором находится эта метка-заменитель, и вложенные блоки будут удалены.

Обработка условных блоков

Условный блок — это часть SQL запроса между фигурными скобками < и >.

1. Блок будет удалён (со всеми вложенными блоками), если в нём есть хотябы одна метка-заменитель, название которой нет в ключах переданного массива меток-заменителей или её значение равно BIND_SKIP .
2. Иначе блок будет сохранён (вложенные блоки независимы). При этом:
   • Если значение метки-заменителя НЕ равно BIND_KEEP , то метка будет заменена на квотированное значение.
   • Если значение метки-заменителя равно BIND_KEEP , то метка будет удалена. Фактически BIND_KEEP удобно использовать только для условных меток-заменителей.

Пример SQL запроса, который можно настроить для получения табличных данных или итогового количества записей: