SQL Snippet for transaction query

SQL snippets in SSMS is a very handy (thouth probably underused) feature to automate some repetative tasks.  A collection of some useful snippets can be found at https://github.com/asathkumara/SSMS17-Code-Snippets

I decided to add a snippet to SSMS for frequently used query template like this

<?xml version="1.0" encoding="utf-8" ?>

<CodeSnippets  xmlns="http://schemas.microsoft.com/VisualStudio/2005/CodeSnippet">

<_locDefinition xmlns="urn:locstudio">

    <_locDefault _loc="locNone" />

    <_locTag _loc="locData">Title</_locTag>

    <_locTag _loc="locData">Description</_locTag>

    <_locTag _loc="locData">Author</_locTag>

    <_locTag _loc="locData">ToolTip</_locTag>

   <_locTag _loc="locData">Default</_locTag>

</_locDefinition>

       <CodeSnippet Format="1.0.0">

              <Header>

                     <Title>Transaction Query Snippet</Title>

                        <Shortcut></Shortcut>

                     <Description>Code Snippet for Transaction Query</Description>

                     <Author>Pranav Kulkarni</Author>

                     <SnippetTypes>

                                <SnippetType>Expansion</SnippetType>

                     </SnippetTypes>

              </Header>

              <Snippet>

                     <Declarations>

                                                       <Literal>

                                  <ID>query_statement</ID>

                                  <ToolTip>Enter your Query statement(s) here.</ToolTip>

                                  <Default>[...]</Default>

                                </Literal>

                                                       <Literal>

                                  <ID>verify_statement</ID>

                                  <ToolTip>Enter your verify statement(s) here.</ToolTip>

                                  <Default>[...]</Default>

                                </Literal>

                                                      

 

                     </Declarations>

                     <Code Language="SQL"><![CDATA[

DECLARE @exec int = 1

DECLARE @commit int = 0

DECLARE @verify int = 0

 

BEGIN TRAN

IF @exec = 1 BEGIN

       -- Query Statements --

       $query_statement$

END

if @verify = 1 begin

       -- Verify Statements --

       $verify_statement$

END

IF @commit = 1 BEGIN

       COMMIT TRAN

END

ELSE BEGIN

       ROLLBACK TRAN

END

 

                     ]]>

                     </Code>

              </Snippet>

       </CodeSnippet>

</CodeSnippets>