T-SQL Tricks – Custom Templates

I wrote about the Template Explorer, which comes with the SQL Server tools and is visible in Management Studio (SSMS). It’s handy, but there are limited code items in there. What if I want more?

That’s easy. Suppose I decide that I often need to create procedures with the EXECUTE AS clause. I usually do this:

CREATE PROCEDURE MyProc
   @id INT
WITH EXECUTE AS OWNER
AS
BEGIN

-- do work
BEGIN TRY

  COMMIT
END TRY
BEGIN CATCH
  ROLLBACK
  EXEC uspErrorHandler;
END CATCH

END

It’s a basic template of stuff I do. Let’s stick this in our Template Explorer.

The first thing I do is go to the Stored Procedure folder. I can right click it and I’ll see this:

templates12

I choose template and a new one is created. I enter a name and I have a template. The first 6 templates here are defaults. The last one, highlighted below, is the one I created.

templates13

Now I right click it again and select Edit. At this point, it will open in a query window. This is just a file in my file system (under ), and like any other query, I can edit it. I paste in my script from above, and change a few items to parameters.

templates14

Now I can save this, and the next time I need this, just drag it into the main window and customize it.

templates15

About way0utwest

Editor, SQLServerCentral
This entry was posted in Blog and tagged , . Bookmark the permalink.

3 Responses to T-SQL Tricks – Custom Templates

  1. I had in fact filed a suggestion with MS Connect to allow easy import of templates into the Template Explorer (https://connect.microsoft.com/SQLServer/feedback/details/668497/ability-to-import-templates-deploy-custom-templates-into-the-template-explorer) – the current option is quite messy and error-prone (due to the level of human interaction required) – especially if you want to share the same set of templates across an entire development team.

    The Connect item unfortunately was marked as “Will Not Fix” and I ended up thinking that not many people are still using it (after SQL 2012). Happy to note that it is still being used and I am not the only one to try an import a custom template into the template explorer.

    Like

  2. Gabe says:

    More recent versions of SSMS and VS allow for the use of snippets, to which you can bind these templates as well to save a few click strokes (although to be honest, I haven’t spent the time to learn Microsoft’s implementation). I have an addon which allows me to bind the code of a template to a keystroke combination. So for instance I can type prc+ and load up all the template code for a brand new procedure.

    Like

Comments are closed.