Mickey's T-SQL Ponderings

First 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.

Begin Transaction

Two 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 insert procedures, so I created this new template called new_iu.

The procedures I’m creating are called by an application, so the template I created provides the following:

• Begin and End Tran
• Rollback in the error handler
• Return statement. (0 is successful, anything else is a failure)
• An output parameter that returns the number of rows changed. We needed this, because more that one kind of row was updated or inserted, but we only want to count the “main” units.

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:    
            DECLARE @RV as int, @RecordsAffected as int
			EXEC @RV = <schemaowner,string,dbo>.<sprocname,string,> [Parameters],@RecordsAffected OUTPUT
			SELECT @RV as ReturnValue, @RecordsAffected as RecordsAffected
Author:     $USER$
Date:       $DATE$

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

		$CURSOR$--[TSQL Statements]

	COMMIT TRAN

END TRY
BEGIN CATCH
	DECLARE
		@ErrorMessage AS nvarchar(3000)
	   ,@ErrorSeverity AS int

	IF @@TRANCOUNT > 0
	BEGIN
        ROLLBACK TRAN
		SET @RecordsAffected = 0
	END 

	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)

	RETURN 1

END CATCH

SET NOCOUNT OFF
RETURN 0
GO

Commit Transaction

If you have other successful ways to write insert, update, or delete stored procedures, please share in the comments.

Previous Post In Series: SQL Advent Calendar – Day 12 – And Neo Yelled
Next Post In Series: SQL Advent Calendar – Day 14 – Working With Forms in a Macro in ER Studio Data Architect