TSQL.APP Multi-Step Process Implementation Guide

Introduction
Architecture Overview
Implementation Details
Complete Code Examples
Setup Guide
Best Practices
Troubleshooting
Extensions & Advanced Usage

Introduction

Purpose

This guide demonstrates how to implement multi-step processes in TSQL.APP using a centralized flow control pattern. This approach provides:

Clear process flow management
Standardized parameter passing
Robust error handling
Easy maintenance and extension

Core Concepts

Main action controls overall flow
Sub-actions perform specific tasks
Standardized parameter passing using JSON
Centralized state management

Architecture Overview

Process Flow Diagram

┌────────────────┐
│   Main Action  │◄─────────────┐
└───────┬────────┘              │
        │                       │
        ▼                       │
┌────────────────┐      Returns to
│  Sub-Action 1  │──────Main with
└───────┬────────┘      Result
        │                       │
        ▼                       │
┌────────────────┐             │
│  Sub-Action 2  │─────────────┘
└────────────────┘

Component Roles

Main Action
- Maintains process state
- Decides next steps
- Handles flow control
- Manages completion
Sub-Actions
- Execute specific tasks
- Return results to main
- No flow control logic
- Simple success/error status
Parameter Exchange
- JSON-based
- Standardized structure
- Clear naming conventions
- Minimal necessary data

Implementation Details

State Management

Process Steps

Initial state / menu
First sub-action completed
Second sub-action completed
Process complete

Status Tracking

ProcessStep: Current step number
ProcessResult: Result from sub-actions

Parameter Structure

Main to Sub-Action

{
    "@wrapped_parameters": {
        "startTime": "2024-02-07 14:54:02",
        "sourceAction": "main_action",
        "processType": "orders"
    }
}

Sub-Action to Main

{
    "@wrapped_parameters": {
        "processResult": "SUCCESS"  // or "ERROR"
    }
}

Complete Code Examples

Main Action Code

-- Main Action: Process Controller

-- 1. Variable declarations (ALL at start)
-- Action configuration (customize these!)
DECLARE @MainActionName NVARCHAR(MAX) = N'main_action';     -- Change this into the actual modal action name!
DECLARE @SubActionName_1 NVARCHAR(MAX) = N'process_orders'; -- Change this into the actual modal sub_action name!
DECLARE @SubActionName_2 NVARCHAR(MAX) = N'view_reports';   -- Change this into the actual modal sub_action name!
--DECLARE @SubActionName_3... (etc.)

-- System variables
DECLARE @ActionButton1 NVARCHAR(MAX);
DECLARE @ActionButton2 NVARCHAR(MAX);
DECLARE @Message NVARCHAR(MAX);
DECLARE @Title NVARCHAR(MAX);
DECLARE @ButtonClass1 NVARCHAR(100);
DECLARE @ButtonClass2 NVARCHAR(100);
DECLARE @ButtonValue1 NVARCHAR(100);
DECLARE @ButtonValue2 NVARCHAR(100);
DECLARE @wrapped_parameters NVARCHAR(MAX);
DECLARE @CurrentTime NVARCHAR(100);
DECLARE @JsonValues NVARCHAR(MAX);

-- Flow control
DECLARE @ProcessStep INT;
DECLARE @ProcessResult NVARCHAR(100);

-- 2. Synchronize with modal state (MANDATORY)
EXEC sp_api_modal_get_value @name=N'@ActionButton1', @value=@ActionButton1 OUT;
EXEC sp_api_modal_get_value @name=N'@ActionButton2', @value=@ActionButton2 OUT;
EXEC sp_api_modal_get_value @name=N'@wrapped_parameters', @value=@wrapped_parameters OUT;

-- Get process state if returning from sub-action
IF @wrapped_parameters IS NOT NULL
BEGIN
    SELECT @ProcessResult = JSON_VALUE(@wrapped_parameters, '$.processResult');
END

-- Initialize or get current process step
EXEC sp_api_modal_get_value @name=N'@ProcessStep', @value=@ProcessStep OUT;
IF @ProcessStep IS NULL SET @ProcessStep = 0;

-- 4. Begin error handling
BEGIN TRY
    -- If we get an error result from any step, show error and reset
    IF @ProcessResult = N'ERROR'
    BEGIN
        SET @ProcessStep = 0;  -- Reset to start
        SET @ProcessResult = NULL;
    END
    
    -- If we get success from a step, advance to next
    IF @ProcessResult = N'SUCCESS'
    BEGIN
        SET @ProcessStep = @ProcessStep + 1;
        SET @ProcessResult = NULL;
    END

    -- Handle different steps of the process
    IF @ProcessStep = 0  -- Initial state / main menu
    BEGIN
        -- Display title and buttons
        SET @Title = N'Choose Process';
        EXEC sp_api_modal_text @text=@Title, @class=N'h3';

        EXEC sp_api_modal_button 
            @name = N'@ActionButton1',
            @value = N'Start Processing',
            @valueout = @ActionButton1 OUT,
            @class = N'btn-primary',
            @inline = 1;

        -- Handle button click
        IF @ActionButton1 IS NOT NULL
        BEGIN
            SET @ProcessStep = 1;

            -- Store process step for next round
            SET @JsonValues = (
                SELECT [@ProcessStep]=@ProcessStep
                FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
            );
            
            -- Start first sub-action
            SET @wrapped_parameters = (
                SELECT 
                    startTime = CONVERT(NVARCHAR(100), GETDATE(), 120),
                    sourceAction = @MainActionName,
                    processType = N'orders'
                FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
            );

            SET @JsonValues = (
                SELECT [@wrapped_parameters]=@wrapped_parameters 
                FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
            );
            
            EXEC sp_api_modal_run 
                @action_name = @SubActionName_1,
                @values = @JsonValues;
                
            RETURN;
        END
    END
    
    ELSE IF @ProcessStep = 1  -- After first sub-action
    BEGIN
        -- Start second sub-action
        SET @wrapped_parameters = (
            SELECT 
                startTime = CONVERT(NVARCHAR(100), GETDATE(), 120),
                sourceAction = @MainActionName,
                processType = N'reports'
            FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
        );

        SET @JsonValues = (
            SELECT [@wrapped_parameters]=@wrapped_parameters 
            FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
        );
        
        EXEC sp_api_modal_run 
            @action_name = @SubActionName_2,
            @values = @JsonValues;
            
        RETURN;
    END
    
    ELSE IF @ProcessStep = 2  -- All steps completed
    BEGIN
        SET @Message = N'All processing steps completed successfully!';
        EXEC sp_api_toast @text=@Message, @class=N'btn-success';
        EXEC sp_api_modal_clear;
        RETURN;
    END

END TRY
BEGIN CATCH
    SET @Message = ERROR_MESSAGE();
    EXEC sp_api_toast @text=@Message, @class=N'btn-danger';
    RETURN;
END CATCH

Sub-Action Code

-- Sub-Action Example ("process_orders")

-- 1. Variable declarations (ALL at start)
DECLARE @wrapped_parameters NVARCHAR(MAX);
DECLARE @Message NVARCHAR(MAX);
DECLARE @Title NVARCHAR(MAX);
DECLARE @StartTime NVARCHAR(100);
DECLARE @SourceAction NVARCHAR(100);
DECLARE @ProcessType NVARCHAR(100);
DECLARE @JsonValues NVARCHAR(MAX);
DECLARE @MainActionName NVARCHAR(MAX) = N'main_action';  -- Must match main action name!
DECLARE @ResultChoice NVARCHAR(MAX);

--local vars:
DECLARE @T NVARCHAR(MAX);

-- 2. Synchronize with modal state (MANDATORY)
EXEC sp_api_modal_get_value @name=N'@wrapped_parameters', @value=@wrapped_parameters OUT;
EXEC sp_api_modal_get_value @name=N'@ResultChoice', @value=@ResultChoice OUT;

-- 3. Parse wrapped parameters
SELECT
    @StartTime = JSON_VALUE(@wrapped_parameters, '$.startTime'),
    @SourceAction = JSON_VALUE(@wrapped_parameters, '$.sourceAction'),
    @ProcessType = JSON_VALUE(@wrapped_parameters, '$.processType');

-- 4. Begin error handling
BEGIN TRY
    -- Display information
    SET @Title = N'Process Orders';
    EXEC sp_api_modal_text @text=@Title, @class=N'h3';
    
    -- Show the received parameters
    SET @T=CONCAT(N'Started at: ', @StartTime)
    EXEC sp_api_modal_text 
        @text=@T, 
        @class=N'text-primary';
    
    SET @T=CONCAT(N'Called from: ', @SourceAction)
    EXEC sp_api_modal_text 
        @text=@T, 
        @class=N'text-info';
    
    SET @T=CONCAT(N'Process type: ', @ProcessType)
    EXEC sp_api_modal_text 
        @text=@T, 
        @class=N'text-info';

    -- Let user choose the result
    EXEC sp_api_modal_choose 
        @name = N'@ResultChoice',
        @value = @ResultChoice OUT,
        @list = N'SUCCESS,ERROR',
        @class = N'mt-3';

    -- If user made a choice, return to main action
    IF @ResultChoice IS NOT NULL
    BEGIN
        -- Return to main action with chosen result
        SET @wrapped_parameters = (
            SELECT 
                processResult = @ResultChoice
            FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
        );

        SET @JsonValues = (
            SELECT [@wrapped_parameters]=@wrapped_parameters 
            FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
        );
        
        -- Return to main action
        EXEC sp_api_modal_run 
            @action_name = @MainActionName,
            @values = @JsonValues;
    END

END TRY
BEGIN CATCH
    SET @Message = ERROR_MESSAGE();
    EXEC sp_api_toast @text=@Message, @class=N'btn-danger';
    
    -- Return to main with error status
    SET @wrapped_parameters = (
        SELECT 
            processResult = N'ERROR'
        FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
    );

    SET @JsonValues = (
        SELECT [@wrapped_parameters]=@wrapped_parameters 
        FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
    );
    
    EXEC sp_api_modal_run 
        @action_name = @MainActionName,
        @values = @JsonValues;
    RETURN;
END CATCH

Setup Guide

1. Create Actions

Main Action Setup
- Create new action in TSQL.APP
- Name it according to process (e.g., 'process_controller')
- Copy main action code
- Update action names in variables
Sub-Actions Setup
- Create action for each step
- Copy sub-action template
- Customize processing logic
- Update MainActionName variable

2. Configuration

Action Names

DECLARE @MainActionName NVARCHAR(MAX) = N'your_main_action';     
DECLARE @SubActionName_1 NVARCHAR(MAX) = N'your_sub_action_1'; 
DECLARE @SubActionName_2 NVARCHAR(MAX) = N'your_sub_action_2';

Process Steps
- Review step numbering
- Adjust flow as needed
- Add/remove steps

3. Testing

Basic Flow
- Start main action
- Test SUCCESS path
- Verify process completion
Error Handling
- Test ERROR responses
- Verify reset behavior
- Check error messages

Best Practices

1. Code Organization

Variable Declaration
- ALL variables at start
- Clear grouping
- Descriptive names
Error Handling
- Proper TRY/CATCH
- Clear messages
- Graceful recovery

2. Flow Control

State Management
- Clear step numbers
- Simple progression
- Error reset logic
Parameter Passing
- Minimal necessary data
- Clear JSON structure
- Proper synchronization

3. Maintenance

Documentation
- Clear comments
- Step descriptions
- Parameter explanations
Code Structure
- Logical grouping
- Consistent formatting
- Clear flow

Troubleshooting

Common Issues

Parameter Problems
- Check JSON structure
- Verify synchronization
- Validate parameter names
Flow Issues
- Verify step numbers
- Check state handling
- Review progression logic

Debug Tips

Add Status Messages

SET @Message = CONCAT(N'Current step: ', @ProcessStep);
EXEC sp_api_toast @text=@Message, @class=N'btn-info';

Check Parameters

SET @Message = CONCAT(N'Parameters: ', @wrapped_parameters);
EXEC sp_api_toast @text=@Message, @class=N'btn-info';

Extensions & Advanced Usage

Adding Steps

New Sub-Action

DECLARE @SubActionName_3 NVARCHAR(MAX) = N'new_step';

Additional Step Handler

ELSE IF @ProcessStep = 3  -- New step
BEGIN
    SET @wrapped_parameters = (
        SELECT 
            startTime = CONVERT(NVARCHAR(100), GETDATE(), 120),
            sourceAction = @MainActionName,
            processType = N'new_process'
        FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
    );

    SET @JsonValues = (
        SELECT [@wrapped_parameters]=@wrapped_parameters 
        FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
    );
    
    EXEC sp_api_modal_run 
        @action_name = @SubActionName_3,
        @values = @JsonValues;
        
    RETURN;
END

Conditional Flow Control

Step Branching

IF @ProcessResult = N'SUCCESS'
BEGIN
    -- Check conditions for next step
    IF EXISTS (SELECT 1 FROM your_table WHERE status = N'pending')
    BEGIN
        SET @ProcessStep = 4;  -- Branch to special handling
    END
    ELSE
    BEGIN
        SET @ProcessStep = @ProcessStep + 1;  -- Normal flow
    END
END

Dynamic Next Step

-- Get next step from business rules
DECLARE @NextStep INT;
SELECT @NextStep = next_step_id 
FROM process_flow_rules 
WHERE current_step = @ProcessStep 
AND condition_met = 1;

SET @ProcessStep = ISNULL(@NextStep, @ProcessStep + 1);

Adding Parameters

Extended Parameter Structure

SET @wrapped_parameters = (
    SELECT 
        startTime = CONVERT(NVARCHAR(100), GETDATE(), 120),
        sourceAction = @MainActionName,
        processType = N'orders',
        customData = (
            SELECT 
                priority = N'high',
                category = N'retail',
                maxItems = 100
            FOR JSON PATH
        )
    FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
);

Reading Complex Parameters

DECLARE @Priority NVARCHAR(100);
DECLARE @Category NVARCHAR(100);
DECLARE @MaxItems INT;

SELECT
    @Priority = JSON_VALUE(@wrapped_parameters, '$.customData.priority'),
    @Category = JSON_VALUE(@wrapped_parameters, '$.customData.category'),
    @MaxItems = JSON_VALUE(@wrapped_parameters, '$.customData.maxItems');

UI Enhancements

Progress Indicator

-- In main action
DECLARE @TotalSteps INT = 3;
DECLARE @ProgressText NVARCHAR(MAX);

SET @ProgressText = CONCAT(N'Step ', @ProcessStep + 1, N' of ', @TotalSteps);

EXEC sp_api_modal_text 
    @text = @ProgressText,
    @class = N'text-muted mb-3';

Step Navigation

-- Allow going back a step
EXEC sp_api_modal_button 
    @name = N'@BackButton',
    @value = N'← Previous Step',
    @valueout = @BackButton OUT,
    @class = N'btn-secondary',
    @inline = 1;

IF @BackButton IS NOT NULL
BEGIN
    SET @ProcessStep = @ProcessStep - 1;
    -- Reset state and restart
    EXEC sp_api_modal_restart;
    RETURN;
END

Advanced Error Handling

Detailed Error Logging

-- In catch block
DECLARE @ErrorNumber INT = ERROR_NUMBER();
DECLARE @ErrorLine INT = ERROR_LINE();
DECLARE @ErrorMessage NVARCHAR(MAX) = ERROR_MESSAGE();

INSERT INTO process_error_log (
    process_step,
    error_number,
    error_line,
    error_message,
    process_parameters
)
VALUES (
    @ProcessStep,
    @ErrorNumber,
    @ErrorLine,
    @ErrorMessage,
    @wrapped_parameters
);

Recovery Options

-- Allow retry or skip on error
IF @ProcessResult = N'ERROR'
BEGIN
    EXEC sp_api_modal_text 
        @text = N'Error occurred. Choose action:',
        @class = N'text-danger';

    EXEC sp_api_modal_choose 
        @name = N'@ErrorAction',
        @value = @ErrorAction OUT,
        @list = N'RETRY,SKIP,RESET',
        @class = N'mt-3';

    IF @ErrorAction = N'RETRY'
        SET @ProcessStep = @ProcessStep;  -- Stay on current step
    ELSE IF @ErrorAction = N'SKIP'
        SET @ProcessStep = @ProcessStep + 1;  -- Skip to next
    ELSE IF @ErrorAction = N'RESET'
        SET @ProcessStep = 0;  -- Start over
END

Integration Patterns

External System Calls

-- Make API call and handle response
DECLARE @ApiResponse NVARCHAR(MAX);
DECLARE @ApiStatus INT;

EXEC sp_api_fetch_json
    @url = N'https://api.example.com/process',
    @method = N'POST',
    @body = @wrapped_parameters,
    @json = @ApiResponse OUTPUT;

SELECT @ApiStatus = JSON_VALUE(@ApiResponse, '$.status');

IF @ApiStatus = 200
    SET @ProcessResult = N'SUCCESS';
ELSE
    SET @ProcessResult = N'ERROR';

File Processing

-- Handle file upload in sub-action
DECLARE @FileId INT;

EXEC sp_api_modal_file
    @name = N'@ProcessFile',
    @to_file_context = N'process_docs',
    @to_file_context_id = @ProcessStep,
    @api_files_id = @FileId OUTPUT;

IF @FileId IS NOT NULL
BEGIN
    -- Process uploaded file
    -- Your file processing logic here
    SET @ProcessResult = N'SUCCESS';
END

Maintenance & Monitoring

Performance Monitoring

Step Timing

DECLARE @StepStartTime DATETIME = GETDATE();

-- Process step logic here...

INSERT INTO step_performance_log (
    step_number,
    execution_time_ms,
    parameters
)
VALUES (
    @ProcessStep,
    DATEDIFF(MILLISECOND, @StepStartTime, GETDATE()),
    @wrapped_parameters
);

Process Analytics

-- Track process metrics
INSERT INTO process_analytics (
    process_id,
    step_number,
    step_result,
    execution_date,
    user_id
)
VALUES (
    @ProcessId,
    @ProcessStep,
    @ProcessResult,
    GETDATE(),
    @user_id
);

Maintenance Tasks

Clean Up Old Data

-- Cleanup job
DELETE FROM process_analytics 
WHERE execution_date < DATEADD(DAY, -30, GETDATE());

DELETE FROM process_error_log
WHERE error_date < DATEADD(DAY, -90, GETDATE());

Process Monitoring

-- Monitor for stuck processes
INSERT INTO process_alerts (process_id, alert_type, alert_message)
SELECT 
    process_id,
    N'STUCK_PROCESS',
    N'Process has not advanced in over 1 hour'
FROM process_state
WHERE last_update < DATEADD(HOUR, -1, GETDATE())
AND status = N'RUNNING';

Conclusion

This implementation guide provides a robust foundation for building multi-step processes in TSQL.APP. The pattern can be extended and customized while maintaining:

Clear flow control
Proper error handling
Clean parameter passing
Easy maintenance

Remember to:

Keep the main action focused on flow control
Keep sub-actions simple and focused
Handle errors gracefully
Document all customizations
Monitor performance and errors

For additional support or questions, consult the TSQL.APP documentation or contact the development team.