I have always found
it very useful to create database coding standards and making it very easy for
myself and others to apply those standards is critical, one of those standards
is applying a default comment header block to SQL Server Stored Procedures.
In previous versions
of Visual Studio I found it very handy to create a procedure template for new
stored procedure creation and distribute that temple to my team members for
easy implementation. In Visual Studio SSDT
(SQL Server Data Tools) when adding a new stored procedure to a database
project, Visual Studio derives a default stored procedure from a template file.
Here is a sample of
what I like to see each time a procedure is created
The scaffolding code
is generated from a template located here
C:\Program Files
(x86)\Microsoft Visual Studio
14.0\Common7\IDE\Extensions\Microsoft\SQLDB\Extensions\SqlServer\Items
NOTE: This path is
very specific to Visual Studio 2015 SSDT.
The files Create
Procedure.sql and Create Procedure.vstemplate are what do the magic. Make sure you make a backup of the Create
Procedure.sql file for safe keeping.
Edit the Create
Procedure.sql file and include any comment text or other common items you find
yourself typing each time you create a procedure. For example BEGIN and END statements, setting
NOCOUNT ON; NOCOUNT OFF;, etc.
Because of
permission issues save this modified file to a DIFFERENT folder
Now delete the
original file from the Visual Studio path
Copy the file
modified file back into the directory
Restart Visual
Studio
Now each time you
create a new stored procedure your boilerplate code will be waiting for
you.
