SQL Server Stored Procedure Custom Templates SSMS and Visual Studio

Problem

When writing SQL Server stored procedures, people have different opinions about T-SQL coding styles [1]. Organizations often recommend some coding conventions and enforce these conventions through code review. A good practice is to create stored procedure templates so that everyone in an organization uses the same templates to write stored procedures. Some T-SQL development tools, such as Microsoft SQL Server Management Studio (i.e., SSMS) and Visual Studio 2019, provide built-in templates; however, each organization has specific requirements. Therefore, we want to add custom stored procedure templates to these tools. We should design templates that others can accept, install, and use. With this in mind, two questions come up: “What content should a stored procedure template have?” and “How can we add the template to SSMS and Visual Studio 2019?”

Solution

Coding conventions are necessary for successful software development. Many professionals have contributed their programming experience to the global SQL Server community. Sheldon [1] introduced some excellent T-SQL coding practices. Worthen [2] and Spaghettidba [3] shared their templates. Microsoft SQL Docs also provides some sample stored procedures [4]. We can incorporate all these recourses and produce templates for our organizations. Designing templates is an evolving and collaborative process; we improve these templates based on changing technologies and resource knowledge. In this tip, we introduce a stored procedure template. We encourage users to test and update the template.

To encourage others to efficiently use this template, we explore a process that adds the custom stored procedure template to SSMS (v18.6). To add the template to Visual Studio 2019, we also present instructions in a comfortable, step-by-step format.

Introducing a Custom Stored Procedure Template

To demonstrate how to use stored procedure templates in SSMS, we take the following steps to create a stored procedure using a default template:

Launch SSMS. In the “Object Explorer” pane, expand a database node, as shown in Figure 1.

Figure 1 Create a Stored Procedure Using the Default Template

Right-click on the “Stored Procedure” node. Navigate to the menu item “New -> Stored Procedure…” in the context menu. Select the menu item to create a stored procedure that uses the default template:

-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE <Procedure_Name, sysname, ProcedureName> 
   -- Add the parameters for the stored procedure here
   <@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
BEGIN
   -- SET NOCOUNT ON added to prevent extra result sets from
   -- interfering with SELECT statements.
   SET NOCOUNT ON
 
    -- Insert statements for procedure here
   SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2>
END
GO

The symbol “<>” denotes a template parameter. We can change the values of these parameters directly in the code editor. An alternative way is to use “Query -> Specify Values for Parameters…” menu command. Access the menu item “Specify Values for Parameters…,” as shown in Figure 2.

Figure 2 Access the Menu Item to Specify Values for Template Parameters

Click on the menu item to bring up the “Specify Values for Template Parameters” dialog, as shown in Figure 3. We assign parameter values in this dialog. Click on the “OK” button to generate a new stored procedure. The default template is beneficial; at least, we do not need to write a stored procedure from scratch.

Figure 3 Specify Values for Template Parameters Dialog

When we write more stored procedures for an organization, gradually, patterns appear. For example, nearly every stored procedure contains a try-catch block to handle errors. When a stored procedure implements a series of database changes, all these changes are in one logical operation. We want to commit to all changes only when all operations are successful; otherwise, we cancel all changes. When we write new stored procedures, we want to follow these patterns. Therefore, we compile these patterns into a template:

IF EXISTS(SELECT name FROM sysobjectsWHERE name = '<Procedure_Name, sysname, ProcedureName>' AND type = 'P')
   DROP PROCEDURE <Procedure_Name, sysname, ProcedureName>
GO
 
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
-- =============================================
-- Author:        <Author,,Name>
-- Create date:  <Create Date,,>
-- Description:   <Description,,>
-- =============================================
--Change History
--Date   Changed by      Description
 
*/
CREATE PROCEDURE <Procedure_Name, sysname, ProcedureName> 
   -- Add the parameters for the stored procedure here
   <@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
BEGIN
    SET NOCOUNT ON
    BEGIN TRY
      BEGIN TRAN
      -- Insert statements for procedure here
 
      COMMIT TRAN
   END TRY
   BEGIN CATCH
      IF @@TRANCOUNT > 0
         ROLLBACK TRAN
      DECLARE @ErrorMessage NVARCHAR(4000
      DECLARE @ErrorSeverity INT
      DECLARE @ErrorState INT
 
      SELECT @ErrorMessage = ERROR_MESSAGE(),
            @ErrorSeverity = ERROR_SEVERITY(),
            @ErrorState = ERROR_STATE
      RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState 
   END CATCH
END
GO

Adding the Custom Stored Procedure Template to SQL Server Management Studio

We used the default stored procedure template to create a new stored procedure in the “Object Explorer” pane. We can also use templates through the pane “Template Explorer.” If the pane is hidden, we can display the pane by selecting the menu item “View -> Template Explorer,” as shown in Figure 4.

Figure 4 Access the Template Explorer

The "Template Explorer" pane with the title "Template Browser" exhibits many T-SQL templates. In this text, we call this pane "Template Explorer". As shown in Figure 5, we find the “Create Stored Procedure (New Menu)” under the stored procedure node.

Figure 5 Template Browser

Double-click on the “Create Stored Procedure (New Menu)” node to create a new stored procedure using this built-in template. To add the custom template under the “Stored Procedure” node, we right-click on the “Stored Procedure” node and select “New -> Template” from the context menu, as shown in Figure 6. Click on the “Template” menu item to create a new template with default name “New SQL Server Template”.

Figure 6 The New Template Command

Rename the new “New SQL Server Template” to “My Custom Template” or any other meaningful name. Right-click on the new template and select the “Edit” item in the context menu to activate the template for modification, as shown in Figure 7.

Figure 7 Create an Empty Template

Copy the custom stored procedure template to the code editor. Save the template by clicking on the save icon on the toolbar. We can view the location of this template when we move the mouse over the tab header, as shown in Figure 8. Through the “Template Explorer” pane, we can use this custom template in the same manner as other built-in templates.

Figure 8 Edit the Custom Template

When we use SSMS (v18.6), all these templates we see in the “Template Explorer” pane are in the folder:

C:\Users\<UserName>\AppData\Roaming\Microsoft\SQL Server Management Studio\18.0\Templates\Sql\

SSMS copies SQL templates to this folder when users launch the “Template Explorer” pane. To verify this fact, we can move all templates in the folder “C:\Users\<UserName>\AppData\Roaming\Microsoft\SQL Server Management Studio\18.0\Templates\Sql\Stored Procedure\” to a temporary folder; the Stored Procedure folder becomes empty. We then close SSMS and reopen it. All templates removed show up in the folder again.

When creating a new stored procedure in the “Object Explorer” pane, we use the template that is in this folder if we install SSMS in C drive:

C:\Program Files (x86)\SQL Server Management Studio 18\Common7\IDE\SqlWorkbenchProjectItems\Sql

To use the custom template when creating a new stored procedure in the “Object Explorer,” we can modify the template “C:\Program Files (x86)\SQL Server Management Studio 18\ Common7\IDE\SqlWorkbenchProjectItems\Sql\Stored Procedure\Create Stored Procedure (New Menu).sql”. It worth noting that we should keep a copy of the original file before making any changes. When we follow the steps shown in Figure 1 to create a new stored procedure, the new one should use the custom template.

Adding the Custom Stored Procedure Template to Visual Studio 2019

In Visual Studio 2019, we can take the following steps to create a stored procedure using a built-in template:

Launch a database project. In the "Solution Explorer" pane, locate a folder where we want to place the new stored procedure. Right-click on the folder. Navigate to the menu item "Add -> Stored Procedure…" in the context menu, as shown in Figure 9.

Figure 9 Navigate to the Menu Item to Create a Stored Procedure in Visual 2019

Select the menu item “Stored Procedure…” to bring up the “Add New Item” dialog, as shown in Figure 10.

Figure 10 Add New Item Dialog

Enter the name of the stored procedure. Click on the “Add” button to create the new stored procedure:

CREATE PROCEDURE [dbo].[uspDeleteProduct]
   @param1 int = 0,
   @param2 int
AS
   SELECT @param1, @param2
RETURN 0

We generated a stored procedure by using the built-in template. Because of the reasons we mentioned in Section 1, we want to use a custom template that looks like the following code. The procedure name "$safeitemname$" is the reserved template parameter [5]. The template uses the file name as the stored procedure name when we create a stored procedure.

CREATE PROCEDURE [dbo].[$safeitemname$] 
   -- Add the parameters for the stored procedure here
   @param1 int = 0,
   @param2 int
AS
BEGIN
    SET NOCOUNT ON
    BEGIN TRY
      BEGIN TRAN
      -- Insert statements for procedure here
 
      COMMIT TRAN
   END TRY
   BEGIN CATCH
      IF @@TRANCOUNT > 0
         ROLLBACK TRAN
      DECLARE @ErrorMessage NVARCHAR(4000
      DECLARE @ErrorSeverity INT
      DECLARE @ErrorState INT
 
      SELECT @ErrorMessage = ERROR_MESSAGE(),
             @ErrorSeverity = ERROR_SEVERITY(),
             @ErrorState = ERROR_STATE
      RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState 
   END CATCH
END

We replace the stored procedure "uspDeleteProduct" in the code editor with the custom template and save all changes. Locate the "Project -> Export Template…" menu item, as shown in Figure 11.

Figure 11 Prepare to Export the Template

Select the menu item “Exports Template…” to bring up the “Export Template Wizard” dialog, as shown in Figure 12.

Figure 12 Export Template Wizard

Since we want to create an item template, we select the item template option and click on the “Next” button. The “Select Item to Export” dialog appears. Select the stored procedure we just created, as shown in Figure 13.

Figure 13 Select Item to Export

Click on the “Next” button to move to the next dialog, as shown in Figure 14. Since this template does not have any reference, we click on the “Next” button to move forward.

Figure 14 Select Item Reference

The last dialog in this wizard is to select template options. As shown in Figure 15, we enter template name and description and leave the “Automatically import the template in Visual Studio” unchecked.

Figure 15 Select Template Options

Click on the “Finish” button to export the template to the folder “C:\Users\<UserName>\Documents\Visual Studio 2019\My Exported Templates\”. To determine where we put template files, we select the menu item “Tools -> Options” to bring up the “Options” dialog, as shown in Figure 16.

Figure 16 Template Location

We access the item templates location “C:\Users\<username>\Documents\Visual Studio 2019\Templates\ItemTemplates\” and drill down into the folder “Extensibility.” Create a child folder “Custom Templates” and copy the exported template into this folder. The folder structure should look like Figure 17.

Figure 17 The Location of the Template

Close the Visual Studio 2019 and relaunch it. Follow the steps illustrated in Figure 9 to bring up the “Add New Item” dialog. The dialog should look like Figure 18. On the left pane, there is a group “Custom Templates.” The custom stored procedure template is in the group. We can use this custom template in the same way as we use other built-in templates.

Figure 18 Use the Custom Stored Procedure Template

Select the procedure name “uspUpdateProduct”; click on the add button. We create a new stored procedure shown in Figure 19 by using the custom template.

Figure 19 Use the Custom Template to Create a Stored Procedure

Summary

In this tip, we have barely scratched the surface of T-SQL coding conventions, but almost everyone adopts coding styles somewhat. To write readable code and maintain proper consistency, all people producing code for an organization should follow specific guidelines. A good practice is to use templates in the organization. SSMS and Visual Studio 2019 provide built-in templates. They also allow us to add custom templates.

We described the process of using built-in templates to create a stored procedure. To add more features into the built-in templates, we introduced a custom stored procedure template that can handle errors and transactions. Then, we presented step-by-step instructions to add custom templates to SSMS and Visual Studio 2019.

References

[1] Sheldon, R. (2017). The Basics of Good T-SQL Coding Style. Retrieve from Redgate Hub: https://www.red-gate.com/simple-talk/sql/t-sql-programming/basics-good-t-sql-coding-style/.

[2] Worthen, J. (2015). A Good Stored Procedure Template. Retrieve from jackworthen.com: https://jackworthen.com/2015/10/29/a-good-stored-procedure-template/.

[3] Spaghettidba (2011). My stored procedure code template. Retrieve from spaghettidba: https://spaghettidba.com/2011/07/08/my-stored-procedure-code-template/.

[4] Guyer, C., Sparkman, M., Hamilton, B., Rabeler, C., Ghanayem, M., Kumar, S., Howell, J., & Milener, G. (2017). TRY…CATCH (Transact-SQL). Retrieved from SQL Docs: https://docs.microsoft.com/en-us/sql/t-sql/language-elements/try-catch-transact-sql?view=sql-server-ver15.

[5] Warren, G., Hogenson, G., Cai, S., Sebolt, M., Wells, J., Jones, M., Jacobs, M., & Liew, V. G. (2018). Template parameters. Retrieved from SQL Docs: https://docs.microsoft.com/en-us/visualstudio/ide/template-parameters?view=vs-2019.

Next Steps

This tip placed focus on adding custom stored procedure templates to SSMS and Visual Studio 2019. With templates, code looks consistent, and team members can easily, quickly understand code. Also, using templates can promote best practices in an organization. There are many templates on the web. We should pick some and start to use them. Developing templates is an evolving and collaborative process. We have better templates when we frequently use them and continuously improve them.
Check out these related tips:

Resources

Community

Subscribe