custom DoCMObjectActionForWorkFlowState¶
This example shows a custom procedure triggered by a workflow state or event handler that performs an action on a specific object using the context menu (Action Type 4 or 5). It takes the object identifiers as parameters, performs updates, and logs progress and results using MFSQL logging helpers.
The procedure demonstrates best practices for:
Comprehensive error handling and logging
Proper use of dynamic SQL with security considerations
Structured code organization with clear sections
Integration with MFSQL framework logging and process tracking
/*
=====================================================================================
Custom Context Menu Action for Workflow State Changes
=====================================================================================
Purpose: Triggered by workflow state or event handler to perform actions on objects
Author: [Author Name]
Created: [Date]
Modified: [Date] - [Description of changes]
Parameters:
@ID INT - Context menu ID
@OutPut VARCHAR(1000) - OUTPUT message for user display
@ProcessBatch_ID INT - Batch logging identifier
@ObjectID INT - M-Files object ID
@ObjectType INT - M-Files object type
@ObjectVer INT - M-Files object version
@ClassID INT - M-Files class ID
Returns: 0 on success, -1 on error
Example Usage:
DECLARE @Output VARCHAR(1000), @BatchID INT = 1;
EXEC custom.DoCMObjectActionForWorkFlowState
@ID = 1, @OutPut = @Output OUTPUT, @ProcessBatch_ID = @BatchID,
@ObjectID = 12345, @ObjectType = 0, @ObjectVer = 1, @ClassID = 78;
=====================================================================================
*/
PRINT SPACE(5) + QUOTENAME(@@SERVERNAME) + '.' + QUOTENAME(DB_NAME()) + '.custom.DoCMObjectActionForWorkFlowState';
GO
SET NOCOUNT ON;
-- Check if procedure exists
IF EXISTS (
SELECT 1
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_NAME = 'DoCMObjectActionForWorkFlowState'
AND ROUTINE_TYPE = 'PROCEDURE'
AND ROUTINE_SCHEMA = 'custom'
)
BEGIN
PRINT SPACE(10) + '...Stored Procedure: update';
SET NOEXEC ON;
END
ELSE
PRINT SPACE(10) + '...Stored Procedure: create';
GO
-- Stub creation (parsed but not executed if procedure exists)
CREATE PROCEDURE custom.DoCMObjectActionForWorkFlowState
AS
SELECT 'created, but not implemented yet.';
GO
-- Main procedure definition
SET NOEXEC OFF;
GO
ALTER PROCEDURE custom.DoCMObjectActionForWorkFlowState
@ID INT,
@OutPut VARCHAR(1000) OUTPUT,
@ProcessBatch_ID INT,
@ObjectID INT,
@ObjectType INT,
@ObjectVer INT,
@ClassID INT
AS
BEGIN
SET NOCOUNT ON;
-- ============================================================================
-- Variable Declarations
-- ============================================================================
DECLARE
@ProcedureName NVARCHAR(128) = 'custom.DoCMObjectActionForWorkFlowState',
@ProcedureStep NVARCHAR(128),
@StartTime DATETIME,
@MFClassTable NVARCHAR(128),
@Update_ID INT,
@SQLQuery NVARCHAR(MAX),
@Params NVARCHAR(MAX),
@LogStatus NVARCHAR(100),
@LogTextDetail NVARCHAR(4000);
BEGIN TRY
-- ====================================================================
-- 1. Initialize Process
-- ====================================================================
SET @OutPut = 'Process Start Time: ' + CAST(GETDATE() AS VARCHAR(50));
SET @StartTime = GETUTCDATE();
-- Get class table name
SELECT @MFClassTable = TableName
FROM dbo.MFClass
WHERE MFID = @ClassID;
IF @MFClassTable IS NULL
THROW 50001, 'Invalid ClassID - no matching table found', 1;
-- Set context menu as running
UPDATE dbo.MFContextMenu
SET IsProcessRunning = 1
WHERE ID = @ID;
-- Log process start
EXEC dbo.spMFProcessBatch_Upsert
@ProcessBatch_ID = @ProcessBatch_ID,
@ProcessType = @ProcedureName,
@LogType = N'Message',
@LogText = @OutPut,
@LogStatus = N'Started',
@debug = 0;
SET @ProcedureStep = 'Initialize Process';
EXEC dbo.spMFProcessBatchDetail_Insert
@ProcessBatch_ID = @ProcessBatch_ID,
@LogType = N'Message',
@LogText = @OutPut,
@LogStatus = N'In Progress',
@StartTime = @StartTime,
@MFTableName = @MFClassTable,
@LogProcedureName = @ProcedureName,
@LogProcedureStep = @ProcedureStep,
@debug = 0;
-- ====================================================================
-- 2. Refresh Object from M-Files
-- ====================================================================
SET @ProcedureStep = 'Refresh from M-Files';
SET @StartTime = GETUTCDATE();
EXEC dbo.spMFUpdateTable
@MFTableName = @MFClassTable,
@UpdateMethod = 1, -- Get from M-Files
@ObjIDs = @ObjectID,
@Update_IDOut = @Update_ID OUTPUT,
@ProcessBatch_ID = @ProcessBatch_ID,
@Debug = 0;
SET @OutPut = 'Refreshed object ' + CAST(@ObjectID AS NVARCHAR(10)) +
' with Update_ID ' + CAST(@Update_ID AS NVARCHAR(10));
EXEC dbo.spMFProcessBatchDetail_Insert
@ProcessBatch_ID = @ProcessBatch_ID,
@LogType = N'Debug',
@LogText = @OutPut,
@LogStatus = N'In Progress',
@StartTime = @StartTime,
@MFTableName = @MFClassTable,
@LogProcedureName = @ProcedureName,
@LogProcedureStep = @ProcedureStep,
@debug = 0;
-- ====================================================================
-- 3. Perform Custom Business Logic
-- ====================================================================
SET @ProcedureStep = 'Update Object Properties';
SET @StartTime = GETUTCDATE();
-- Build dynamic SQL for object update
SET @Params = N'@OutPutParam NVARCHAR(1000), @ObjectIDParam INT';
SET @SQLQuery = N'
UPDATE ct
SET process_ID = 1,
Keywords = ''Updated in '' + ISNULL(@OutPutParam, '''')
FROM ' + QUOTENAME(@MFClassTable) + ' ct
WHERE ct.ObjID = @ObjectIDParam';
EXEC sys.sp_executesql
@SQLQuery,
@Params,
@OutPutParam = @OutPut,
@ObjectIDParam = @ObjectID;
-- ====================================================================
-- 4. Push Changes to M-Files
-- ====================================================================
SET @ProcedureStep = 'Push to M-Files';
SET @StartTime = GETUTCDATE();
EXEC dbo.spMFUpdateTable
@MFTableName = @MFClassTable,
@UpdateMethod = 0, -- Send to M-Files
@ObjIDs = @ObjectID,
@Update_IDOut = @Update_ID OUTPUT,
@ProcessBatch_ID = @ProcessBatch_ID,
@Debug = 0;
SET @OutPut = 'Updated object ' + CAST(@ObjectID AS NVARCHAR(10)) +
' with Update_ID ' + CAST(@Update_ID AS NVARCHAR(10));
EXEC dbo.spMFProcessBatchDetail_Insert
@ProcessBatch_ID = @ProcessBatch_ID,
@LogType = N'Debug',
@LogText = @OutPut,
@LogStatus = N'In Progress',
@StartTime = @StartTime,
@MFTableName = @MFClassTable,
@LogProcedureName = @ProcedureName,
@LogProcedureStep = @ProcedureStep,
@debug = 0;
-- ====================================================================
-- 5. Complete Process
-- ====================================================================
-- Reset context menu status
UPDATE dbo.MFContextMenu
SET IsProcessRunning = 0
WHERE ID = @ID;
-- Final success message
SET @OutPut = @OutPut + ' Process End Time: ' + CAST(GETDATE() AS VARCHAR(50));
-- Log completion
EXEC dbo.spMFProcessBatch_Upsert
@ProcessBatch_ID = @ProcessBatch_ID,
@ProcessType = @ProcedureName,
@LogType = N'Message',
@LogText = @OutPut,
@LogStatus = N'Completed',
@debug = 0;
SET @ProcedureStep = 'Process Completed';
EXEC dbo.spMFProcessBatchDetail_Insert
@ProcessBatch_ID = @ProcessBatch_ID,
@LogType = N'Message',
@LogText = @OutPut,
@LogStatus = N'Success',
@StartTime = GETUTCDATE(),
@MFTableName = @MFClassTable,
@LogProcedureName = @ProcedureName,
@LogProcedureStep = @ProcedureStep,
@debug = 0;
RETURN 0;
END TRY
BEGIN CATCH
-- ====================================================================
-- Error Handling
-- ====================================================================
SET @LogStatus = 'Failed w/SQL Error';
SET @LogTextDetail = 'Error in ' + ISNULL(@ProcedureStep, 'Unknown Step') +
': ' + ERROR_MESSAGE();
SET @OutPut = 'Error: ' + ERROR_MESSAGE();
-- Reset context menu status on error
UPDATE dbo.MFContextMenu
SET IsProcessRunning = 0
WHERE ID = @ID;
-- Log error details
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
);
-- Log error in process batch
EXEC dbo.spMFProcessBatch_Upsert
@ProcessBatch_ID = @ProcessBatch_ID,
@ProcessType = @ProcedureName,
@LogType = N'Error',
@LogText = @LogTextDetail,
@LogStatus = @LogStatus,
@debug = 0;
EXEC dbo.spMFProcessBatchDetail_Insert
@ProcessBatch_ID = @ProcessBatch_ID,
@LogType = N'Error',
@LogText = @LogTextDetail,
@LogStatus = @LogStatus,
@StartTime = GETUTCDATE(),
@LogProcedureName = @ProcedureName,
@LogProcedureStep = ISNULL(@ProcedureStep, 'Error Handler'),
@debug = 0;
RETURN -1;
END CATCH
END