SQL Script Header Footer I use

SQL Script Header Footer I use :

/******************************************************************************************************
** Desc: This File will create the XXXXXXXXXXXXXXXXXXXXXXX .
** Auth: Raju Venkataraman ( [email protected] )
** Date: 09/21/2014 Created
**************************
** Change History
**************************
** CR Date Author Description
** ----- ----------– ----------- ------------------------------------------------------------
** 1 09/25/2014 Raju Venkataraman added Specific Checks to the Script
** 2 09/25/2014 Raju Venkataraman added ErrorHandler Validations
********************************************************************************************************/
SET NOCOUNT ON
SET ARITHABORT ON
SET CONCAT_NULL_YIELDS_NULL ON
SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET NUMERIC_ROUNDABORT OFF

-----------BEGIN SQL SCRIPT HEADER-------------------
DECLARE @DB_NAME VARCHAR(128)
SET @DB_NAME = (SELECT DB_NAME(dbid) FROM MASTER..SYSPROCESSES WHERE spid=@@SPID)
PRINT '-----------------------------------------------------------------------'
PRINT '-----START RAPID DEVELOPEMENT SQL SCRIPT--------'
PRINT '-----SQL COMPILED BY: Raju Venkataraman'
PRINT '-----SCRIPT RAN ON DB: ' + @DB_NAME
PRINT '-----SCRIPT START TIME: ' + CONVERT(VARCHAR,CONVERT(DATETIME,GETDATE()),121)
PRINT '-----MachineName : ' + cast( serverproperty ( 'MachineName' ) as varchar )
PRINT '-----SQL Instance : ' + cast( @@SERVERNAME as varchar )
PRINT '-----DB User : ' + current_user
PRINT '-----System User : ' + system_user
PRINT '-----Host : ' + host_name()
PRINT '-----Application : ' + app_name()
PRINT '-----TranCount : ' + cast (@@trancount as varchar)
PRINT '-----------------------------------------------------------------------'
GO
----------------END SQL SCRIPT HEADER---------------------

----- Standard Variables Declarations

DECLARE
@ErrorMessage NVARCHAR(4000),
@ErrorNumber INT,
@ErrorSeverity INT,
@ErrorState INT,
@ErrorLine INT,
@ErrorProcedure NVARCHAR(200),
@ErrParameter NVARCHAR(255),
@ErrParameterValue NVARCHAR(255)

---- Script Specific Variables Declarations

DECLARE @pParam1 INT



IF NOT EXISTS (
	SELECT * FROM sys.syslogins lgs WHERE loginname = 'DevLogin'
 )

BEGIN

SELECT
@ErrorNumber = 50001 ,
@ErrorSeverity = 16 ,
@ErrorState = 1 ,
@ErrorLine = ERROR_LINE(),
@ErrParameter ='DevLogin Login doesn''t exist on this Server',
@ErrParameterValue = CONVERT(VARCHAR(50) , @pParam1) ,
@ErrorProcedure = ISNULL(ERROR_PROCEDURE(),'-');

GOTO ErrorHandler
END
ELSE
	BEGIN
		PRINT 'DevLogin Login Exists on this Server'
	END

IF @@TRANCOUNT >0

BEGIN

SELECT
@ErrorNumber = 50001 ,
@ErrorSeverity = 16 ,
@ErrorState = 1 ,
@ErrorLine = ERROR_LINE(),
@ErrParameter ='@@TranCont',
@ErrParameterValue = CONVERT(VARCHAR(255) ,'You left the Transction Open!!!. Open Transaction Count ::'+CONVERT(VARCHAR(10) ,@@TRANCOUNT)) ,
@ErrorProcedure = ISNULL(ERROR_PROCEDURE(),'-');

GOTO ErrorHandler
END

GOTO ExitScript

ErrorHandler:

----- Use RAISERROR inside the CATCH block to return error
----- information about the original error that caused
----- execution to jump to the CATCH block.
RAISERROR
(
@ErrorNumber,
@ErrorSeverity,
@ErrorState, ----- parameter: original error state.
@ErrParameter,
@ErrParameterValue
);

ExitScript:

-------BEGIN SQL SCRIPT FOOTER------------------------------------
PRINT '-----------------------------------------------------------'
PRINT '-------------------FINISHED SQL SCRIPT---------------------'
PRINT '--COMPLETED TIME:' + CONVERT(VARCHAR,CONVERT(DATETIME,GETDATE()),121)
PRINT '--TranCount : ' + cast (@@trancount as varchar)
PRINT '------------------------------------------------------------'

------------------------END SQL SCRIPT FOOTER-----------------