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:

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.

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.

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

Filed under: Blog Tagged: syndicated, T-SQL