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.