Template - custom procedure

This is a blank template for starting off a custom procedure providing for logging, debugging and error handling.

PRINT SPACE(5) + QUOTENAME(@@SERVERNAME) + '.' + QUOTENAME(DB_NAME()) + '.schema.procname';
GO
SET NOCOUNT ON

/*------------------------------------------------------------------------------------------------
    Author:
    Create date:
    Database:
    Description:

    PARAMETERS:

-- ----------------------------------------------------------------------------------------------*/
/*------------------------------------------------------------------------------------------------
  MODIFICATION HISTORY
  ====================
     DATE            NAME        DESCRIPTION

-- ----------------------------------------------------------------------------------------------*/
/*-----------------------------------------------------------------------------------------------
  USAGE:
  =====

-- ---------------------------------------------------------------------------------------------*/
IF EXISTS (      SELECT    1
              FROM        INFORMATION_SCHEMA.ROUTINES
              WHERE        ROUTINE_NAME = 'procname' --name of procedure
                        AND ROUTINE_TYPE = 'PROCEDURE' --for a function --'FUNCTION'
                        AND ROUTINE_SCHEMA = 'schema'
          )
    BEGIN
        PRINT SPACE(10) + '...Stored Procedure: update';
        SET NOEXEC ON;
    END;
ELSE PRINT SPACE(10) + '...Stored Procedure: create';
GO

-- if the routine exists this stub creation stem is parsed but not executed
CREATE PROCEDURE schema.procname
AS
    SELECT    'created, but not implemented yet.';
-- just anything will do

GO
-- the following section will be always executed
SET NOEXEC OFF;
GO
ALTER PROCEDURE schema.procname
    (
        @ProcessBatch_ID INT      = NULL OUTPUT
      , @Debug             SMALLINT = 0
    )
AS
    BEGIN
        SET NOCOUNT ON;

        -- -----------------------------------------------------------
        -- CONSTANTS: MFSQL Class Table Specific
        -- -----------------------------------------------------------
        DECLARE @MFTableName AS NVARCHAR(128) = 'ClassTable'
        DECLARE @ProcessType AS NVARCHAR(50);

        SET @ProcessType = ISNULL(@ProcessType, 'ProcessType')

        -- -----------------------------------------------------------
        -- CONSTATNS: MFSQL Global
        -- -----------------------------------------------------------
        DECLARE @UpdateMethod_1_MFilesToMFSQL TINYINT = 1
        DECLARE @UpdateMethod_0_MFSQLToMFiles TINYINT = 0
        DECLARE @Process_ID_1_Update TINYINT = 1
        DECLARE @Process_ID_6_ObjIDs TINYINT = 6 --marks records for refresh from M-Files by objID vs. in bulk
        DECLARE @Process_ID_9_BatchUpdate TINYINT = 9 --marks records previously set as 1 to 9 and update in batches of 250
        DECLARE @Process_ID_Delete_ObjIDs INT = -1 --marks records for deletion
        DECLARE @Process_ID_2_SyncError TINYINT = 2
        DECLARE @ProcessBatchSize INT = 250

        -- -----------------------------------------------------------
        -- VARIABLES: MFSQL Processing
        -- -----------------------------------------------------------
        DECLARE @Update_ID INT
        DECLARE @Update_IDOut INT
        DECLARE @MFLastModified DATETIME
        DECLARE @MFLastUpdateDate Datetime
        DECLARE @Validation_ID int

        -- -----------------------------------------------------------
        -- VARIABLES: T-SQL Processing
        -- -----------------------------------------------------------
        DECLARE @rowcount AS INT = 0;
        DECLARE @return_value AS INT = 0;
        DECLARE @error AS INT = 0;

        -- -----------------------------------------------------------
        -- VARIABLES: DEBUGGING
        -- -----------------------------------------------------------
        DECLARE @ProcedureName AS NVARCHAR(128) = 'schema.procname';
        DECLARE @ProcedureStep AS NVARCHAR(128) = 'Start';
        DECLARE @DefaultDebugText AS NVARCHAR(256) = 'Proc: %s Step: %s'
        DECLARE @DebugText AS NVARCHAR(256) = ''
        DECLARE @Msg AS NVARCHAR(256) = ''
        DECLARE @MsgSeverityInfo AS TINYINT = 10
        DECLARE @MsgSeverityObjectDoesNotExist AS TINYINT = 11
        DECLARE @MsgSeverityGeneralError AS TINYINT = 16

        -- -----------------------------------------------------------
        -- VARIABLES: LOGGING
        -- -----------------------------------------------------------
        DECLARE @LogType AS NVARCHAR(50) = 'Status'
        DECLARE @LogText AS NVARCHAR(4000) = '';
ECLARE @LogStatus AS NVARCHAR(50) = 'Start'

        DECLARE @LogTypeDetail AS NVARCHAR(50) = 'System'
        DECLARE @LogTextDetail AS NVARCHAR(4000) = '';
        DECLARE @LogStatusDetail AS NVARCHAR(50) = 'In Progress'
        DECLARE @ProcessBatchDetail_IDOUT AS INT = NULL

        DECLARE @LogColumnName AS NVARCHAR(128) = NULL
        DECLARE @LogColumnValue AS NVARCHAR(256) = NULL

        DECLARE @count INT = 0;
        DECLARE @Now AS DATETIME = GETDATE();
        DECLARE @StartTime AS DATETIME = GETUTCDATE();
        DECLARE @StartTime_Total AS DATETIME = GETUTCDATE();
        DECLARE @RunTime_Total AS DECIMAL(18, 4) = 0;

        -- -----------------------------------------------------------
        -- VARIABLES: DYNAMIC SQL
        -- -----------------------------------------------------------
        DECLARE @sql NVARCHAR(MAX) = N''
        DECLARE @sqlParam NVARCHAR(MAX) = N''

        -- -----------------------------------------------------------
        -- INTIALIZE PROCESS BATCH
        -- -----------------------------------------------------------
        SET @ProcedureStep = 'Start Logging'

        SET @LogText = 'Processing ' + @ProcedureName

        EXEC dbo.spMFProcessBatch_Upsert
            @ProcessBatch_ID = @ProcessBatch_ID OUTPUT
          , @ProcessType = @ProcessType
          , @LogType = N'Status'
          , @LogText = @LogText
          , @LogStatus = N'Start'
          , @debug = @Debug

        EXEC dbo.spMFProcessBatchDetail_Insert
            @ProcessBatch_ID = @ProcessBatch_ID
          , @LogType = N'Debug'
          , @LogText = @ProcessType
          , @LogStatus = N'Started'
          , @StartTime = @StartTime
          , @MFTableName = @MFTableName
          , @Validation_ID = @Validation_ID
          , @ColumnName = NULL
          , @ColumnValue = NULL
          , @Update_ID = @Update_ID
          , @LogProcedureName = @ProcedureName
          , @LogProcedureStep = @ProcedureStep
        , @ProcessBatchDetail_ID = @ProcessBatchDetail_IDOUT
          , @debug = 0

        BEGIN TRY
            -- -----------------------------------------------------------
            -- BEGIN PROCESS
            -- -----------------------------------------------------------
            SET @DebugText = ''
            Set @DebugText = @DefaultDebugText + @DebugText
            Set @Procedurestep = ''

            IF @debug > 0
                Begin
                    RAISERROR(@DebugText,10,1,@ProcedureName,@ProcedureStep );
                END

            -- -----------------------------------------------------------
            -- END PROCESS
            -- -----------------------------------------------------------
            END_RUN:
            SET @ProcedureStep = 'End'
            Set @LogStatus = 'Completed'
            -- -----------------------------------------------------------
            -- Log End of Process
            -- -----------------------------------------------------------

         EXEC dbo.spMFProcessBatch_Upsert
                @ProcessBatch_ID = @ProcessBatch_ID
              , @ProcessType = @ProcessType
          , @LogType = N'Status'
              , @LogText = @LogText
              , @LogStatus = @LogStatus
              , @debug = @Debug

            SET @StartTime = GETUTCDATE()

            EXEC dbo.spMFProcessBatchDetail_Insert
                @ProcessBatch_ID = @ProcessBatch_ID
              , @LogType = N'Debug'
              , @LogText = @ProcessType
              , @LogStatus = @LogStatus
              , @StartTime = @StartTime
              , @MFTableName = @MFTableName
              , @Validation_ID = @Validation_ID
              , @ColumnName = NULL
              , @ColumnValue = NULL
              , @Update_ID = @Update_ID
              , @LogProcedureName = @ProcedureName
              , @LogProcedureStep = @ProcedureStep
              , @debug = 0
            RETURN 1
        END TRY
        BEGIN CATCH
            SET @StartTime = GETUTCDATE()
            SET @LogStatus = 'Failed w/SQL Error'
            SET @LogTextDetail = ERROR_MESSAGE()

            -- ------------------------------------------------
            -- INSERTING ERROR DETAILS INTO LOG TABLE
            -- ------------------------------------------------
            INSERT INTO dbo.MFLog ( SPName
                                      , ErrorNumber
                                      , ErrorMessage
                                      , ErrorProcedure
                                      , ErrorState
                                      , ErrorSeverity
                                      , ErrorLine
                                      , ProcedureStep
                                      )
            VALUES (
                       @ProcedureName
                     , ERROR_NUMBER()
                     , ERROR_MESSAGE()
                     , ERROR_PROCEDURE()
                     , ERROR_STATE()
                     , ERROR_SEVERITY()
                     , ERROR_LINE()
                     , @ProcedureStep
                   );

            SET @ProcedureStep = 'Catch Error'
            -- -----------------------------------------------------------
            -- Log Error
            -- -----------------------------------------------------------
            EXEC dbo.spMFProcessBatch_Upsert
                @ProcessBatch_ID = @ProcessBatch_ID OUTPUT
              , @ProcessType = @ProcessType
              , @LogType = N'Error'
              , @LogText = @LogTextDetail
              , @LogStatus = @LogStatus
              , @debug = @Debug

            SET @StartTime = GETUTCDATE()

            EXEC dbo.spMFProcessBatchDetail_Insert
                @ProcessBatch_ID = @ProcessBatch_ID
              , @LogType = N'Error'
              , @LogText = @LogTextDetail
              , @LogStatus = @LogStatus
              , @StartTime = @StartTime
              , @MFTableName = @MFTableName
              , @Validation_ID = @Validation_ID
              , @ColumnName = NULL
              , @ColumnValue = NULL
              , @Update_ID = @Update_ID
              , @LogProcedureName = @ProcedureName
              , @LogProcedureStep = @ProcedureStep
              , @debug = 0

            RETURN -1
        END CATCH

    END

GO