Thursday, 13 June 2013

MSSQL - Query Templates

Often working with the database we have to write lot of typical queries.
In this case it is convenient to create templates of such requests.
SQL Server Management Studio (SSMS) gives us that opportunity.

First of all, make sure that you have activated the tab "Templates Explorer":
[Menu] - [View] - [Template Explorer] or <Control>+<Alt>+<T>:

SSMS will show us lot of ready-to-use templates:

For example in the folder "SQL Server Templates" - "Stored Procedure" template "Create Procedure Basic Template". Click on it with double click or click with right mouse button and select from the context menu item "Open". SMS will open a new T-SQL Scripting window with this text:

In this script, there are some expressions in the form of <Name, DataType, DefaultValue>.
You can fill/replace these placeholders with help of corresponding dialog.
Just call a [Menu]-[Query]-[Specify Values for Template Parameters...]:

or press the button on the toolbar:

In the dialog, you can set new values ​​for the placeholders or leave the default values:

After you click [OK], the placeholders will be replaced with the appropriate values​​:

You can extend templates directory with your custom templates. Just call a context menu for the appropriate folder:

Or copy the folder with your templates (for example @inovex) in this folder:
C:\Users\<Your User>\AppData\Roaming\Microsoft\Microsoft SQL Server\<MSSQL Version>\Tools\Shell\Templates\Sql\

In future articles, I will show examples of some useful templates.