As you have known by now, I am currently running a series of posts inspired by Andy Warren’s editorial in SQLServerCentral.com on the "Underappreciated features of Microsoft SQL Server".
Continuing our journey through some of the features of Microsoft SQL Server Management Studio, we will today look at a feature that I am sure will increase developer productivity by leaps and bounds.
The Template Explorer
Per Microsoft SQL Server Books On Line, Template Explorer is a component in SQL Server Management Studio that presents templates to quickly construct code in the Code Explorer. The templates are grouped by the type of code being created, which in turn makes finding the right templates blazingly fast.
Navigating through the Template Explorer
To launch the Template Explorer launch the SQL Server Management Studio and simply use the Ctrl+Alt+T keyboard combination or go to View->Template Explorer.
| Launching the Template Explorer |
 |
SQL Server provides the user the option to choose from any of the 3 families of templates:
- SQL Server
- Analysis Services
- SQL Compact
NOTE: On SQL Server 2008, the first time the template explorer is opened, a copy of the templates is placed in the users Documents and Settings folder under Application DataMicrosoftMicrosoft SQL Server100ToolsShellTemplates.
SQL Server Templates
For the purposes of this example, we will attempt to create a new stored procedure using SQL Server templates. All one needs to do is to navigate out to “Stored Procedures” folder in the Template Explorer and double-click on “Create Procedure Basic Template”.
The first thing that strikes us is that they contain parameters to help us customize the code. Per BOL, template parameter definitions use this format <parameter_name, data_type, value>, where:
- parameter_name is the name of the parameter in the script
- data_type is the data type of the parameter
- value is the value that is to replace every occurrence of the parameter in the script
Replacing Parameters in SQL Server Templates
Replacing the parameters in a SQL Server Template to create an almost fully-functional script is a simple three step process, as under:
- On the Query menu, click Specify Values for Template Parameters
- In the Specify Values for Template Parameters dialog box, the Values column contains suggested value for the parameter. Accept the value or replace it with a new value as required, and then click OK to close the Replace Template Parameters dialog box and modify the script in the query editor
- Modify the query to add whatever business logic necessary
| Launching the “Specify Values for Template Parameters” dialog | Replacing Template Parameters |
 |
 |
Here’s the script with the parameters replaced. The SELECT statement in the body of the stored procedure has been replaced with business logic:
-- =============================================
-- Create basic stored procedure template
-- =============================================
-- Drop stored procedure if it already exists
IF EXISTS (
SELECT *
FROM INFORMATION_SCHEMA.ROUTINES
WHERE SPECIFIC_SCHEMA = N'[Sales]'
AND SPECIFIC_NAME = N'[proc_AddNumbers]'
)
DROP PROCEDURE [Sales].[proc_AddNumbers]
GO
CREATE PROCEDURE [Sales].[proc_AddNumbers]
@p1 int = 0,
@p2 int = 0
AS
--Template Default
--SELECT @p1, @p2
SELECT @p1+@p2 AS 'Result'
GO
-- =============================================
-- Example to execute the stored procedure
-- =============================================
EXECUTE [Sales].[proc_AddNumbers] 1, 2
GO
As you can see, our basic script is now ready for use in just 3 steps!
Creating custom templates
Most organizations have very specific coding standards and thus, if a mechanism could be devised to tweak the default templates to suit their needs, the feature would be extremely helpful to them. This can very well be done as per the following steps mentioned in SQL Server Books On Line:
- In Template Explorer, navigate to the node where you would like to store the new template
- Right-click the node, point to New, and then click Template
- Type the name for your new template and then press ENTER
- Right-click the new template, and then click Edit. In the Connect to Database Engine dialog box, click Connect to open the new template in Query Editor
- Create a script in Query Editor. Insert parameters in your script in the format <parameter_name, data_type, value>
- On the toolbar, click Save to save your new template
NOTE: When defining parameters, the data type and value areas must be present, but can be blank.
Let’s follow these steps to customize the “Create Stored Procedure Basic Template” as under (in the below script, replace ‘< ‘ with ‘<’ and ‘ >’ with ‘>’. The site kept messing up the parameter identifiers.):
/*
********************************************************************************************************
Developed By : < Developer_Name, , Developer_Name >
Functionality : < Functionality, , Functionality >
Template : Create Procedure Basic Template
Modifications :
< Creation_Date, DATE, Creation_Date > - < Developer_Initials, , Developer_Initials > - Created
********************************************************************************************************
*/
-- Drop stored procedure if it already exists
IF EXISTS (
SELECT *
FROM INFORMATION_SCHEMA.ROUTINES
WHERE SPECIFIC_SCHEMA = N'< Schema_Name, sysname, Schema_Name >'
AND SPECIFIC_NAME = N'< Procedure_Name, sysname, Procedure_Name >'
)
DROP PROCEDURE < Schema_Name, sysname, Schema_Name >.< Procedure_Name, sysname, Procedure_Name >
GO
CREATE PROCEDURE < Schema_Name, sysname, Schema_Name >.< Procedure_Name, sysname, Procedure_Name >
< @param1, sysname, @p1 > < datatype_for_param1, , int > = < default_value_for_param1, , 0 >,
< @param2, sysname, @p2 > < datatype_for_param2, , int > = < default_value_for_param2, , 0 >
AS
SELECT @p1, @p2
GO
-- =============================================
-- Example to execute the stored procedure
-- =============================================
EXECUTE < Schema_Name, sysname, Schema_Name >.< Procedure_Name, sysname, Procedure_Name > < value_for_param1, , 1 >, < value_for_param2, , 2 >
GO
Following the above mentioned process to replace template parameters, we can then produce the following:
/*
********************************************************************************************************
Developed By : Nakul Vachhrajani
Functionality : Adds two integers
Template : Create Procedure Basic Template
Modifications :
January 09, 2011 - NAV - Created
********************************************************************************************************
*/
-- Drop stored procedure if it already exists
IF EXISTS (
SELECT *
FROM INFORMATION_SCHEMA.ROUTINES
WHERE SPECIFIC_SCHEMA = N'[Sales]'
AND SPECIFIC_NAME = N'[proc_AddNumbers]'
)
DROP PROCEDURE [Sales].[proc_AddNumbers]
GO
CREATE PROCEDURE [Sales].[proc_AddNumbers]
@p1 int = 0,
@p2 int = 0
AS
--Template Default
--SELECT @p1, @p2
SELECT @p1+@p2 AS 'Result'
GO
-- =============================================
-- Example to execute the stored procedure
-- =============================================
EXECUTE [Sales].[proc_AddNumbers] 1, 2
GO
In Conclusion
According to me, Template Explorer is one of the most powerful productivity enhancing tools after Intelli-sense. For those who are still using Microsoft SQL Server 2005, Template Explorer is also available there.
To all the DBAs reading this, if you haven’t already done so, please create your own templates as per your organization’s standards. Once done, please distribute these amongst developers and train them on how to use the Template Explorer.
I am sure developers will love to use this feature of Microsoft SQL Server. Do share the feedback that you receive from the developers.
In my next post, I will be looking at the Activity Monitor. Till then,
Be courteous. Drive responsibly.
SQLTwins by Nakul Vachhrajani 