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