SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

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


Filed under: Blog Tagged: syndicated, T-SQL

The Voice of the DBA

Steve Jones is the editor of SQLServerCentral.com and visits a wide variety of data related topics in his daily editorial. Steve has spent years working as a DBA and general purpose Windows administrator, primarily working with SQL Server since it was ported from Sybase in 1990. You can follow Steve on Twitter at twitter.com/way0utwest

Comments

Leave a comment on the original post [voiceofthedba.wordpress.com, opens in a new window]

Loading comments...