SQL Advent Calendar 2013 – Day 11 – Snippet For a New Stored Procedure

AdventCalendar11First Post in this series: SQL Advent Calendar 2013-Day 1- Placeholders in SQL Prompt Snippets

As I was hanging our family Advent Calendar up, I thought I would make one for you, my readers. I’ll be sharing my scripts from two of my favorite products as well as tips on how to create your own.

Save the Key Strokes!

The snippet below has saved me from a ton of typing! Just three little letters and TAB.

  • I always have a comment block.
  • I never have to add my name or date to the comment block.
  • My error handler always looks the same.
  • I even have a place for my test parameters.

This snippet, called affectionately New, provides the outer shell for a new stored procedure. It is ideal for non-transactional stored procedures. (I will later share my latest snippet created for transactional stored procedures.) It’s set up as a DROP / CREATE, and the DROP is wrapped in an IF statement so that it doesn’t fail the first time. Your cursor is placed right where your query needs to go. CTRL+SHIFT+M will populate the other unique parts of the script like database name, stored procedure name, etc.

USE <DatabaseName,string,$DBNAME$>
GO

--Use Shift + Ctrl + M to set Template Parameters

IF EXISTS ( SELECT * FROM sys.objects WHERE	object_id = OBJECT_ID(N'<schemaowner,string,dbo>.<sprocname,string,>') AND type IN (N'P', N'PC') ) 
	DROP PROCEDURE <schemaowner,string,dbo>.<sprocname,string,>
GO
/* ***********************************************************************************
Purpose:    <purpose,string,[brief description]="">
Notes:      
Sample:    
            EXEC <schemaowner,string,dbo>.<sprocname,string,> [Parameters]
Author:     $USER$
Date:       $DATE$

Revision History
(Change Date)	(Author)		(Description of Change)
-----------------------------------------------------------------
************************************************************************************ */
CREATE PROCEDURE <schemaowner,string,dbo>.<sprocname,string,> 
(
	--Insert Parameters
)
AS 
BEGIN TRY
	SET NOCOUNT ON
----------------------------------------------------
-------           Test Parameters           --------
----------------------------------------------------
--DECLARE 
--		@StartDate datetime = '12/1/2010'
--		,@EndDate datetime = '12/5/2010'
----------------------------------------------------
----------------------------------------------------

		$CURSOR$--[TSQL Statements]

END TRY
BEGIN CATCH

	DECLARE
	    @ErrorMessage AS nvarchar(3000)
	   ,@ErrorSeverity AS int

	SET @ErrorMessage = ISNULL(DB_NAME(DB_ID()) + N'.' + SCHEMA_NAME(SCHEMA_ID()) + N'.' + OBJECT_NAME(@@PROCID, DB_ID()),
						N'SQL Object Name Not Available') + N': Error: ' + CONVERT(nvarchar(10), ERROR_NUMBER()) + N' Line: ' 
						+ CONVERT(nvarchar(5), ERROR_LINE()) + N' - ' + ERROR_MESSAGE()   

	SET @ErrorSeverity = ERROR_SEVERITY()
	RAISERROR(@ErrorMessage, @ErrorSeverity, 1)
END CATCH

SET NOCOUNT OFF
GO
If You’re Brave…

Currently the $CURSOR$ placeholder is being used on line 37. This means you can immediately start typing your query. If you’re brave, you can change the placeholder to $PASTE$. This will put whatever is in your copy buffer directly in your new stored procedure. (I’m not that brave. Sometimes I have a lot of Excel Data hanging out, drinking spiked eggnog, and singing Karaoke in my copy buffer.)

 

AdventCalendar2013Day11_Img2

 

One of the cool features of this script, is the way the error handler is created. You can comment out lines 3 to 25 (the AS line)  and the error handler won’t fail. Instead of showing the stored procedure name (that you aren’t using) it will simply tell you, you are not in a stored procedure at the moment.

AdventCalendar2013Day11_Img1

 

I hope you enjoy this gem as much as I do.

Note: You can download this snippet from my Script Library under Resources. It’s called New.

Previous Post In Series: SQL Advent Calendar 2013 – Day 10 – Creating Change Tracking Domains (3 of 3)
Next Post In Series: SQL Advent Calendar–Day 12– And Neo Yelled

2 comments

  1. […] days ago, I shared my snippet to create a shell for new procedures. A week ago, I found I was writing quite a few update and […]

  2. […] Post In Series: SQL Advent Calendar 2013 – Day 11 – Snippet For a New Stored Procedure Next Post In Series: SQL Advent Calendar – Day 13 – Snippet for New Transactional […]

%d bloggers like this: