SQL Templates and Code Snippets in SSMS and SQL Prompt
Phil Factor explains the uses and limitations of native SSMS templates and snippets, and then demonstrates the extra versatility that SQL Prompt snippets provide.
In SSMS, we have access to a couple of ‘boilerplates’ for the SQL code that you need to perform routine database tasks such as creating tables, functions or procedures:
- SSMS templates – provide building blocks for creating various types of query, or for common routines to create various schema objects, or to do common DBA tasks such as running backups.
- SSMS code snippets – SQL snippets that define the basic structure for a block of code or a T-SQL statement that creates the common types of database objects.
Both these features save coding time, and introduce standards and consistency to the way you build certain modules, such as stored procedures or functions. For example, you can use templates and snippets to standardize the structure of any headers, include guard clauses that will delete the module first if it exists before creating the new version, and so on.
However, the native SSMS versions of these features have some serious limitations, which I’ll describe, before going on to demonstrate how SQL Prompt snippets add a versatility and flexibility that I, and I think most programmers, appreciate.
How SSMS Templates work
An SSMS template is a SQL script template for performing a common database management task. We can, for example, use a template to create, modify or drop various schema objects, alter a database audit specification, or add a linked server. In SSMS, just use Ctrl-Alt-T to bring up the Template Browser and locate the one you need. If you open a template, by double-clicking, you create a separate copy of the code in a new query window, and any changes you make won’t affect the original template. To place a template into existing code, you drag and drop it from the template window into the existing query pane. If you want to edit the existing template to better suit your requirements, simply right-click it and choose Edit. To create a new one, choose New | Template.
Listing 1 shows a typical SSMS template for creating an inline function. I’ve edited the built-in template to include a guard clause, which will delete any existing version of the table function, and to use my preferred header structure, which can easily be read by PowerShell.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
IF Object_Id('<Inline_Function_Name, sysname, FunctionName>') IS NOT NULL DROP function <Inline_Function_Name, sysname, FunctionName> GO CREATE FUNCTION <Inline_Function_Name, sysname, FunctionName> ( /** Summary: > <<Description,,> Author: <Author,string,Name> Date: <Create Date,date,1 Jan 2017> Database: <Database,string,Adventureworks> Examples: - Select * from <Inline_Function_Name, sysname, FunctionName> ( <@param1, sysname, @p1>, <@param2, sysname, @p2> Returns: > <returns,string,NVarchar(max)> **/ -- Add the parameters for the function here <@param1, sysname, @p1> <Data_Type_For_Param1, , int>, <@param2, sysname, @p2> <Data_Type_For_Param2, , char> ) RETURNS TABLE AS RETURN ( -- Add the SELECT statement with parameter references here SELECT 0 ) GO |
Listing 1
You will notice the parameter placeholders contained in angle-brackets, with three comma-delimited parameters of the form <Parameter, Type, Value>. If you want to apply the same macro several times, as in the name of the function above, you need to repeat the same definition, identically.
Each time you need to create an inline function, you just open the template, and hit Ctrl-Shift-M, to bring up the form for specifying the template parameter values, such as the name of the object, the names and types of any parameters that the object accepts or returns, and so on.
Now you can just fill in the form with the values you want.
When you hit OK, SSMS will run the macros to replace the whole strong between the angle brackets with the values you supplied. The result looks as shown in listing 2.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
IF Object_Id('TernaryInverse') IS NOT NULL DROP function TernaryInverse GO CREATE FUNCTION TernaryInverse ( /** Summary: > Produces the negative of the ternary number Author: Phil Factor Date: 15 Sept 2017 Database: TernaryMaths Examples: - Select * from TernaryInverse( @TernaryValue, @Negative) Returns: > Varchar(27) **/ -- Add the parameters for the function here @TernaryValue Varchar(27), @Negative Bit ) RETURNS TABLE AS RETURN ( -- Add the SELECT statement with parameter references here SELECT 0 ) GO |
Of course, there is a lot still to do, and the body of the function needs to be stitched in. We can always use an SSMS snippet instead.
How SSMS Code Snippets work
A snippet is any pre-defined block of code, which you can quickly insert into a query window, at the cursor position, just by right-clicking and locating the required snippet. A snippet is like a lightweight template and can be used in a similar fashion, with the advantage that it is added at the current insertion point.
Right-click in the query window choose Insert Snippet… from the context menu and then select the Function folder followed by the type of function you wish to create.
This inserts the snippet, with the various replacement points, within the code highlighted. Each replacement point corresponds to a declared literal within the snippet’s XML file.
Each replacement point has an associated ToolTip with text that suggests the syntax relevant to that point. You can use the Tab key to move between each replacement point.
You can manage all code snippets using the Code Snippets Manager, which you can reach via Tools | Code Snippets Manager…, or by the keyboard shortcut, CTRL+K followed by CTRL+B. From here, you can view the names and descriptions of snippets, remove them individually and import all code snippets.
The Location entry shows where the snippet’s XML file is saved. If you want to modify an existing snippet, say to add a guard clause and header, you’ll need to edit raw XML. Likewise, if you want to import a snippet, you’ll likely have to edit the XML to get it into the right format.
By editing the XML, you can create a snippet for a function that embeds your currently selected text within the body of the function. It isn’t easy, and would take too long to explain in this article.
This brings us to SQL Prompt snippets.
Why ask for more than what’s in SSMS?
SSMS code snippets and templates are a popular feature. Templates are reasonably easy to use from the template window either by drag-and-drop, or by double clicking to open the template in a new query window. One quick keystroke later (Ctrl-Shift-M) and you have a form to fill in with the important details. Fill in the form and all the embedded macros have values in them.
This is useful, but there are some obvious things missing:
- You can’t turn a selected query into a view, function, procedure, trigger or spExecuteSQL batch. You need to be able to select your query and pick the template and have it put the selected text embedded within in the body of the function.
- There is no way to put dynamic macro values in automatically for headers or inline comments. For some of these, you need values such as the date and logged-in developer, or database-wide values such as the database name.
- The template window needs to be visible when you need to use it. With small screens, you tend to want to hide templates away to give yourself more real estate. It was this last factor that seems to have led to the SSMS snippets, even though their functionality overlaps with templates. Just right-click and you’ve got a menu of SQL objects that you can inject as build scripts into your query window at the insertion point. It is quite a lot easier.
SQL Prompt is a more versatile code snipper manager. It offers a type of code snippet that aims to do away with the frustrating restrictions of SSMS. Essentially, with SQL Prompt, you now have three different ways of accessing snippets in SSMS, two native and one provided by SQL Prompt. Each has its advantages and disadvantages. There is some overlap in functionality.
SSMS Template | SSMS Snippet | SQL Prompt Snippet | |
Can you insert currently-selected text? | ❌ | ✅(only into BEGIN…END , IF and WHILE blocks.) |
✅ |
Can you include database-specific macros? | ❌ | ❌ | ✅
($DATE$ , $DBNAME$ , $MACHINE$ , $SERVER$ and $USER$ ) |
Can you create an entry form for macro values? | ✅(if you hit Ctrl-Shift-M) | ❌ | ✅ |
Is it always there when you need it? | ❌ | ✅ | ✅ |
Can a team share its templates/snippets? | ✅ | ❌ | ✅ |
Do you get ‘replacement points’? (you are prompted with a description of the syntax element you can specify at that point) | ❌ | ✅ | ✅(several copies of the same replacement point are allowed) |
Can you create your own snippet? | ✅ | ✅(but only by editing raw XML) | ✅ |
How SQL Prompt Snippets Work
I want a better ‘template’ than is possible with either the SSMS template or the SSMS Snippet. SQL Prompt Snippets make it easier to create snippets, either from scratch, or by creating a snippet from selected text in a query window, via the right-click context menu. It is simple to include multiple replacement points for the same parameter. I can edit a SQL Prompt snippet without having to delve into XML.
SQL Prompt comes with many pre-defined snippets, as well as allowing you to create your own. To open an existing snippet within a query window, simply start typing the snippet’s name, which by convention will be the initial letters of its description, so citf for “create inline table function”.
However, I want my citf snippet, shown in Listing 3, to contain my usual headers and guard clauses. They are a chore to do by hand, and I dislike chores.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
IF Object_Id('$function_name$', 'IF') IS NOT NULL DROP function $function_name$ GO CREATE FUNCTION $function_name$ /** Summary: > <LineOneSummary, String, This is a first line> <LineTwoSummary, String, This is a second line> Author: $USER$ Date: $DATE$ Database: $DBNAME$ Examples: - Select * from $function_name$ - Select * from MyTable cross apply $function_name$(MyColumn) Returns: > <Returns, String, A table> **/ ( @param1 INT, @param2 CHAR(5) ) RETURNS TABLE --WITH ENCRYPTION|SCHEMABINDING, .. AS RETURN ( $SELECTEDTEXT$$CURSOR$ ) |
Notice that that the function name needs to be inserted three times, at least. If you want to give examples of usage in the header, then you will need even more.
In the header, I’ve used some normal SSMS template parameters, within angle brackets, as well as some built-in Prompt placeholder macros to auto-fill the connected user and database, and the current date. I’ve even included some example executions.
At the point where I need to define the body of the function, I’ve used the $SELECTEDTEXT$
placeholder, which means that any selected text in an SSMS query window can be inserted into the body of the function
To replace SQL Prompt’s built-in citf snippet with my own, I just need to edit it in SQL Prompt Snippet Manger (SQL Prompt | Snippet manager).
Hit Save, then OK. Now, In SSMS, we write a query that will find all the addresses in a city, for the AdventureWorks
database.
1 2 3 4 5 6 7 |
SELECT AddressID, AddressLine1 + COALESCE(',' + AddressLine2, '') + ', ' + City + ', ' + StateProvinceCode + ', ' + PostalCode AS Address FROM Person.Address AS A INNER JOIN Person.StateProvince AS SP ON SP.StateProvinceID = A.StateProvinceID WHERE City LIKE @City |
With the text of Listing 4 highlighted in the query window, click on the red speech bubble, called the Prompt action list, and select the citf snippet.
Immediately, we’re confronted with the SSMS form for specifying the template parameter values.
I fill it in, bang the OK button, and can then fill in the function name. I just need to highlight and fill in the first one and SQL Prompt replaces all occurrences.
Now I tidy it up a little bit, to give it the right parameters, and execute it.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 |
IF Object_Id('dbo.AddressesInCity', 'IF') IS NOT NULL DROP function dbo.AddressesInCity GO CREATE FUNCTION dbo.AddressesInCity /** Summary: > This table function lists out the addresses in the city that you specify in the @city parameter Author: PhilFactor Date: 18/09/2017 Database: AdventureWorks2012 Examples: - Select * from dbo.AddressesInCity('paris') - Select * from MyAddresses cross apply dbo.AddressesInCity(CityColumn) Returns: > A table that lists all the matching addresses **/ ( @City NVARCHAR(80) ) RETURNS TABLE --WITH ENCRYPTION|SCHEMABINDING, .. AS RETURN ( SELECT AddressID, AddressLine1+ Coalesce(','+AddressLine2,'')+', '+ City+', '+ StateProvinceCode+', '+PostalCode AS address FROM Person.Address AS A INNER JOIN Person.StateProvince AS SP ON SP.StateProvinceID = A.StateProvinceID WHERE city LIKE @City ) |
We can now highlight the example in the header and hit ‘execute’ in SSMS to check it
Job Done!
Conclusion
I hope this has demonstrated that the SQL Prompt Snippets have considerable power to save you a lot of time. Some DBAs store all their most common DBA checks in them, and a developer like me uses them to try to aim for a better quality of code in the same amount of time. If you’re told that you don’t need SQL Prompt snippets because what is in SSMS is good enough, I’d just give them a sad, pitying look.
One curiosity about the SQL Prompt snippets for me is that so few of the examples really stretch the capabilities of the system and I found it hard to find worked examples of everything I wanted to do. It is worth experimenting.