Visual Studio 2013 Database Project: Create a Stored Procedure Item Template

Overview

Using Visual Studio for database development requires that database objects like stored procedures be created, amongst other items such as tables, view, triggers, etc.  It is advantageous if creating an item that the proposed script is a template that already conforms to the company’s standards, by providing a boiler-plate script that speeds up the developer’s options.  Even better would be if these templates are available to all developers as they check items in-and-out of Team Foundation Server.  Bear in mind that a particular organisation might have a single development office or location, but others may be geographically dispersed.  It would be expedient if all of them are singing from the same hymnal.

This article explores the basics of Visual Studio Templates for database development.  Using this phrase as a search term, an Internet search engine divvies up a few articles in support.  One of which is called An Overview of Templates for Database and Server Objects (Microsoft).  It suggests that existing templates can be customised to affect all templates used by the team.  Customisable project item/ stubs accelerate development by providing the developer with a head-start. Templates are comprised of project and item templates.  Every time a user opens a menu item in Visual Studio to create something, a template is invoked, e.g. New Project, or Add New Item.

Templates exist in the user community (How to Add Items Using Online Item Templates) or a VSIX project with SideWaffle (Code4Fun), but these aren’t discussed here.  To Illustrate the concept, a template for a Stored Procedure is created and added as an Item Template to the local developer machine.  This is absorbed into Visual Studio and provides the developer with a new item in the Add New Item option.

Creating the Template

A Stored Procedure (via Add New Item ) in Visual Studio (VS) and for that matter in Microsoft SQL Server Management Studio (SSMS) is pretty uneventful.  Therefore, should a developer create quite a few items, the work required to customise these to the company standard absorbs unnecessary time and effort.  A template should make this much easier.

Either use VS or SSMS to create a stored procedure that conforms to all general company standards.  This one to be used in Visual Studio, so the usage between SSMS and VS is different from a parameterisation perspective.

In SSMS the code-block (below) illustrates how parameter are coded and then on invocation provided by Ctrl+Shift+M.  In Visual Studio this is done through an XML configuration and only what the respective dialog requests on New Item is user-provided, e.g. the project or object name.

1 2 3 4 5 6

CREATE PROCEDURE [<Schema_Name, sysname, Schema_Name>].[<Procedure_Name, SYSNAME, ProcedureName>] <@Param1, SYSNAME, @p1> <Datatype_For_Param1, , INTEGER> = <Default_Value_For_Param1, , 0> , <@Param2, SYSNAME, @p2> <Datatype_For_Param2, , INTEGER> = <Default_Value_For_Param2, , 0> , <@Param3, SYSNAME, @p3> <Datatype_For_Param2, , INTEGER OUTPUT> WITH EXECUTE AS [CALLER | SELF | 'user_name'] -- Default: CALLER AS

The flowing code-block represents the Visual Studio equivalent of the obove code for the same purpose.  The $fileinputname$ is the file name provided by the developer when creating a new stored procedure.  All the other parameters are configured in the Item Template’s XML file.  Therefore, the XML configuration should prescribe the purpose of the stored procedure, I.e. suit exactly what it is being created for. 

1 2 3 4 5 6

CREATE PROCEDURE [$SchemaName$].[$fileinputname$] $ProcParam1$ $ProcParam1DataType$ = $ProcParam1Value$ , $ProcParam2$ $ProcParam2DataType$ = $ProcParam2Value$ , $ProcParam3$ $ProcParam3DataType$ $ProcParam3Type$ WITH EXECUTE AS [CALLER | SELF | 'user_name'] -- Default: CALLER <-- make a selection in the executing principle context. AS

A Visual Studio Item Template for a Custom Stored Procedure

The provided template fast-tracks a developer and provides a consistent standard for every stored procedure created base on this template.  This particular standard is comprised of a few sections, viz.

• Create with execution principal context
• Flower box containing the abstract and owner details
• Initial proc-wide directives, e.g. NOCOUNT, EXACABORT, and TRANSACTION ISOLATION
• Error-handling variables (for RAISERROR)
• Local variable declaration & initialisation
• Main query execution block wrapped in TRY. . . CATCH and transaction
• CATCH block with detailed error handling with XACT_STATE
• Return value for application event handling
• Directive switch to system defaults
• Metadata section for object details (metadata) when it’s built in the database

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 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222

CREATE PROCEDURE [$SchemaName$].[$fileinputname$] $ProcParam1$ $ProcParam1DataType$ = $ProcParam1Value$ , $ProcParam2$ $ProcParam2DataType$ = $ProcParam2Value$ , $ProcParam3$ $ProcParam3DataType$ $ProcParam3Type$ WITH EXECUTE AS [CALLER | SELF | 'user_name'] -- Default: CALLER <-- make a selection in the executing principle context. AS /* ================================================================================================================================== ABSTRACT: This procedure executes for $projectname$ and. . . VERSION: $ItemVersion$ (MAJOR.MINOR.PATCH) OWNER: $ItemOwner$ TEMPLATE: $TemplateVersion$ (MAJOR.MINOR.PATCH) <NOTE: to developer, complete the metadata at the end of this procedure script.> << See bottom of item for details >> =================================================================================================================================== */ BEGIN SET NOCOUNT ON; --SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED -- READ COMMITTED -- REPEATABLE READ -- SNAPSHOT -- SERIALIZABLE; -- -------------------------------------------------------------------------------------------------- -- Renders entire txn uncommittable if constraint violation occurs. -- ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_6tsql/html/cbcaa433-58f2-4dc3-a077-27273bef65b5.htm -- -------------------------------------------------------------------------------------------------- SET XACT_ABORT ON; -- ------------------------------------------------------------------------------------ -- VALIDATE INCOMING PARAMETERS -- ------------------------------------------------------------------------------------ -- None. -- ------------------------------------------------------------------------------------- -- DECLARE LOCAL VARIABLES -- ------------------------------------------------------------------------------------- -- Error Handling Local Variables -- Delete this for SQL 2014 unless required for custom error logging DECLARE @vErrorNumber INTEGER = 0 , @vErrorMessage NVARCHAR(2048) = N'' , @vErrorSeverity INTEGER = 0 , @vErrorState TINYINT = 0 , @vErrorLine INTEGER = 0 , @vErrorProcedure NVARCHAR(256) = NULL; -- Processing Local Variables DECLARE @LocalVariable1Name INTEGER , @LocalVariable2Name DATETIME; -- ------------------------------------------------------------------------------------- -- INITIALISE LOCAL VARIABLES -- ------------------------------------------------------------------------------------- -- Initialise processing variables SET @LocalVariable1Name = 0; SET @LocalVariable2Name = '2011-01-01'; /* ========================================================================================= MAIN CODE EXECUTION ============================================================================================ */ BEGIN TRY BEGIN TRANSACTION; -- { sql_statement | statement_block } COMMIT TRANSACTION; RETURN(@vErrorNumber); END TRY BEGIN CATCH /* ------------------------------------------------------------------------------------------ ERROR HANDLING Severity 0-10 (information) are not caught by TRY. . . CATCH Severity 11-16 (user-correctable) Severity 17 (Insufficient Resources) Severity 18 (non-fatal error) Severity 19 (SQL resource error) Severity 20-25 (fatal system error) execution termination with SQL close https://msdn.microsoft.com/en-us/library/ms164086.aspx Any sys.messages valid messages can be utilised but must be passed to RAISERROR or THROW. -------------------------------------------------------------------------------------------- */ -- Grab error indicators. -- Delete this for SQL 2014 unless required for custom error logging SELECT @vErrorNumber = ERROR_NUMBER() , @vErrorLine = ERROR_LINE() , @vErrorSeverity = ERROR_SEVERITY() , @vErrorState = ERROR_STATE() , @vErrorProcedure = ERROR_PROCEDURE() , @vErrorMessage = ERROR_MESSAGE()+ CHAR(10) + N'Executing code item name or type ('+ISNULL(@vErrorProcedure,N'AdHoc SQL') + N'), error number ('+ CONVERT(NVARCHAR(20), @vErrorNumber) + N'), severity ('+CONVERT(NVARCHAR(20), @vErrorSeverity) + N'), code error line ('+CONVERT(NVARCHAR(20),@vErrorLine) + N').'+CHAR(10) +CHAR(13); -- ------------------------------------------------------------------------- -- END TRANSACTION CORRECTLY. -- XACT_STATE = 1 ~ Transaction is committable. -- XACT_STATE = -1 ~ Transaction is uncommittable - roll back -- XACT_STATE = 0 ~ No transaction - commit or rollback results in error. -- ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_6tsql/html/248df62a-7334-4bca-8262-235a28f4b07f.htm -- https://msdn.microsoft.com/en-us/library/ms175976.aspx -- Any custom error loggin should be implemented here -- ------------------------------------------------------------------------- IF (XACT_STATE() = 1) BEGIN COMMIT TRANSACTION; PRINT N'Transaction('+ISNULL(ERROR_PROCEDURE(),N'AdHoc SQL')+N'): COMMITTED'; RETURN(@vErrorNumber); END ELSE BEGIN IF (XACT_STATE() != 0) ROLLBACK TRANSACTION; SET @vErrorMessage = @vErrorMessage + N'Transaction: ROLLED BACK'; -- SQL 2000+ RAISERROR ( @vErrorMessage -- { msg_id | msg_str | @local_variable } , @vErrorSeverity , @vErrorState -- [ ,argument [ ,...n ] ] ) [ WITH option [ ,...n ] ] ) -- SQL Server 2014+ --THROW; RETURN(@vErrorNumber); END END CATCH; -- Cleanup/ Housekeeping SET TRANSACTION ISOLATION LEVEL READ COMMITTED; -- Database Default SET XACT_ABORT OFF; SET NOCOUNT OFF; END /* ================================================================================================================================== TEMPLATE AUTHOR: $TemplateAuthor$, COMPANY: $OwnerCompany$ OBJECT VERSION HISTORY AUTHOR: <Name.Last Name>, ORGANISATION: <Company Name>, AUTHORED DATE <YYYY-MM-DD> VERSION DATE NAME SURNAME CHANGE DETAILS --------- ----------- ----------------- --------------------------------------------------------------------------------------- $ItemVersion$ YYYY-MM-DD Name.Surname Change details are..... << See bottom of item for details >> =================================================================================================================================== */ GO /* --------------------------------------------------------------------------------------------- OBJECT METADATA NOTE: This section has to be edited and run for all attributes after each edit to keep the database metadata up to date. Add extended properties to the view (in database objects) 1. Author name 2. View naming specifics 3. View version 4. Editor name 5. Edit date 6. Template version ----------------------------------------------------------------------------------------------- */ -- Author details EXECUTE sys.sp_addextendedproperty @name = N'Author Name' , @value = N'$ItemAuthor$' --<-- Change this only! , @level0type = N'SCHEMA' , @level0name = N'$SchemaName$' , @level1type = N'PROCEDURE' , @level1name = N'$fileinputname$' , @level2type = NULL , @level2name = NULL; GO -- Object description EXECUTE sys.sp_addextendedproperty @name = N'Functional Description' , @value = N'This procedure executes for $projectname$ and. . .' --<-- Change this only! , @level0type = N'SCHEMA' , @level0name = N'$SchemaName$' , @level1type = N'PROCEDURE' , @level1name = N'$fileinputname$' , @level2type = NULL , @level2name = NULL; GO -- This view version EXECUTE sys.sp_addextendedproperty @name = N'Version' , @value = N'$ItemVersion$' --<-- Change this only! , @level0type = N'SCHEMA' , @level0name = N'$SchemaName$' , @level1type = N'PROCEDURE' , @level1name = N'$fileinputname$' , @level2type = NULL , @level2name = NULL; GO -- Editor details EXECUTE sys.sp_addextendedproperty @name = N'Editor Name' , @value = N'$EditorName$' --<-- Change this only , @level0type = N'SCHEMA' , @level0name = N'$SchemaName$' , @level1type = N'PROCEDURE' , @level1name = N'$fileinputname$' , @level2type = NULL , @level2name = NULL; GO -- Edit date EXECUTE sys.sp_addextendedproperty @name = N'Edit Date' , @value = N'$EditDate$' --<-- Change this only! , @level0type = N'SCHEMA' , @level0name = N'$SchemaName$' , @level1type = N'PROCEDURE' , @level1name = N'$fileinputname$' , @level2type = NULL , @level2name = NULL; GO -- Template version EXECUTE sys.sp_addextendedproperty @name = N'Template version' , @value = N'$TemplateVersion$' --<-- Change this only! , @level0type = N'SCHEMA' , @level0name = N'$SchemaName$' , @level1type = N'PROCEDURE' , @level1name = N'$fileinputname$' , @level2type = NULL , @level2name = NULL; GO -- End --------------------------------------------------------------------------------------

Configuring the Stored Procedure Template in Visual Studio

The template can be created in any SQL editor, viz. SSMS or VS.  Once it is created, open Visual Studio and add a Stored Procedure to an existing Project, using the default VS functionality.

VS Add new Item to Project

Follow these steps:

1. Select Stored Procedure and create it by giving it a name that would suit the template Stored Procedure.
2. Take the newly created template SQL code for Stored Procedure (listed in the code-block above) and paste it over the text of the newly created item.
3. Save it and then open: File >> Export Template
4. Select Item Template radio-button from the [Export Template Wizard] and the appropriate project into which the template Stored Procedure has been saved
5. Click the [Next] button
6. Select the name of the Stored Procedure just created and saved in the project
7. There should be no Item References to select so click the [Next] button
8. Provide a Template Name, description, Icon, Preview Image, and deselect the Automatically import the template into Visual Studio (reference), keep the Display an explorer window on the output files folder selected
9. Click the [Finish] button, and the output files folder is presented

A zip-file is created that contains three files:

1. TemplateIcon.ic
2. MyTemplate.vstemplate
3. <Stored Procedure>.sql (the template SQL)

The MyTemplate.vstempate file is the one used to configure how the stored procedure is created, i.e. provide the name, description, input parameters, and other functions.

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 32 33 34 35

<VSTemplate Version="3.0.0" xmlns="http://schemas.microsoft.com/developer/vstemplate/2005" Type="Item"> <TemplateData> <DefaultName>ACreateStoredProcedure.sql</DefaultName> <Name>ACreateStoredProcedure</Name> <Description>Default CreateStoredProcedure for Acme Corporation</Description> <ProjectType>SQLDB</ProjectType> <SortOrder>10</SortOrder> <Icon>__TemplateIcon.ico</Icon> </TemplateData> <TemplateContent> <References /> <ProjectItem ReplaceParameters="true" TargetFileName="$fileinputname$.sql" >Z Create Stored Procedure.sql</ProjectItem> <CustomParameters> <CustomParameter Name="$projectname$" Value="API"/> <CustomParameter Name="$SchemaName$" Value="dbo"/> <CustomParameter Name="$ProcParam1$" Value="@InputParameter1"/> <CustomParameter Name="$ProcParam1DataType$" Value="INTEGER"/> <CustomParameter Name="$ProcParam1Value$" Value="0"/> <CustomParameter Name="$ProcParam2$" Value="@InputParameter2"/> <CustomParameter Name="$ProcParam2DataType$" Value="INTEGER"/> <CustomParameter Name="$ProcParam2Value$" Value="0"/> <CustomParameter Name="$ProcParam3$" Value="@OutputParameter"/> <CustomParameter Name="$ProcParam3DataType$" Value="INTEGER"/> <CustomParameter Name="$ProcParam3Type$" Value="OUTPUT"/> <CustomParameter Name="$ItemVersion$" Value="0.0.1"/> <CustomParameter Name="$TemplateVersion$" Value="0.0.1"/> <CustomParameter Name="$ItemOwner$" Value="c 2015 Acme (Pty) Ltd."/> <CustomParameter Name="$OwnerCompany$" Value="Acme (Pty) Ltd."/> <CustomParameter Name="$TemplateAuthor$" Value="Hendriekus J.Roux"/> <CustomParameter Name="$ItemAuthor$" Value="Hendriekus J.Roux"/> <CustomParameter Name="$EditorName$" Value="Hendriekus J.Roux"/> <CustomParameter Name="$EditDate$" Value="2015-03-09"/> </CustomParameters> </TemplateContent> </VSTemplate>

The listing of the MyTemplate.vstemplate (above) illustrates two parameter functions, viz. system parameter and custom parameters. These are placed in the template SQL code as place-holders that are filled-out either by providing a name in response to an action in Visual Studio like New >> Project or New >> Item where a name is provided by the developer.  Or, the custom parameters are filled-out in correspondence to the values provided in the MyTemplate.vstemplate file.  The latter should be considered in affinity to the functional intension of the Item, I.e. Stored Procedure in this case.

 Present the Template Available in Visual Studio

Visual Studio present a dialog when a new Stored Procedure is created.

VS Create New Item Dialog Option List

The template should be presented here in a section appropriate to the company and project.  To create a new section the template is added to a folder location on the developer’s machine.  The Visual Studio custom templates are located here: <drive>:\Users\<user name>\Documents\Visual Studio 2013\Templates\ItemTemplates.

VS Custom Templates Folder

By adding templates either into these locations or by creating a new folder, say Acme Items, any new custom templates could be placed there and become available from the New Item dialog.

In this example create a folder here called: ItemTemplates\Acme Corporation\Programmability into which the zip-file (<drive>:\Users\<user>\Documents\Visual Studio 2013\My Exported Templates) is copied.

To use the new template, restart Visual Studio and add a new Stored Procedure to the template using the new template.

VS Custom Template New Item Dialog

Unfortunately, at this point, none of the parameters (except the procedure name) is substituted in the actual SQL code.  These, i.e. the custom parametes must yet be added to the MyTemplate.vstemplate file.

Adding Custom Parameters to a Template

Follow these steps to add these parameters and their values:

1. Extract the template’s zip file from its location into the [My Exported Templates] location and open the  file in Notepad
2. Take the parameters between the <CustomParameters> tag from the code (above) and add it to the current template’s file in the appropriate location
3. Save the file and drag the saved version from the explorer window over the zip file in the template location  and choose the overwrite option to up date the template
4. Restart Visual Studio and create a new Stored Procedure from the template to see all parameters being populated.

Previous Article (3) [Create a Database Project with Folder Structure]. . . Next Article (4) [Coming Soon]

Advertisements