“T-SQL dot app” Multi-Step Forms Implementation Guide

Pizza order APP Screenshot of the complete pizza ordering web app

Core Concepts

  1. State Management

    • Variables persist between steps using JSON
    • Client-server synchronization via sp_api_modal_get_value
    • Step tracking through numerical indicators

  2. Flow Control

    • Each step contained in separate IF block
    • Forward/backward navigation through modal restart
    • Clean return statements prevent code bleed

Best Practices

  1. Always declare all variables at start
  2. Always use Unicode NVARCHAR variables for string concatenation
  3. Always use N'' for string literals
  4. Prepare variables before any concatenation or stored procedure calls
  5. Return after modal restart
  6. Use btn-success/btn-warning for toasts

Variable Management

-- Declare all variables at start
DECLARE @Step INT;
DECLARE @Input1 NVARCHAR(100);
DECLARE @Input2 NVARCHAR(100);
DECLARE @DisplayText NVARCHAR(MAX);
DECLARE @NextButton NVARCHAR(100);
DECLARE @JsonValues NVARCHAR(MAX);

-- Sync all required values
EXEC sp_api_modal_get_value @name='@Step', @value=@Step OUT;
EXEC sp_api_modal_get_value @name='@Input1', @value=@Input1 OUT;

State Transitions

-- Forward Navigation
SET @JsonValues = (
    SELECT 
        [@Step] = @CurrentStep + 1,
        [@Input1] = @Input1,
        [@Input2] = @Input2
    FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
);
EXEC sp_api_modal_restart @values = @JsonValues;

-- Backward Navigation
SET @JsonValues = (
    SELECT 
        [@Step] = @CurrentStep - 1,
        [@Input1] = @Input1
    FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
);
EXEC sp_api_modal_restart @values = @JsonValues;

Error Handling

-- Validate step range
IF @Step < 1 OR @Step > 3
BEGIN
    SET @Step = 1;
END

-- Validate required inputs
IF @Step = 2 AND @Name IS NULL
BEGIN
    SET @Message = N'Please complete step 1 first';
    EXEC sp_api_modal_text @text=@Message;
    SET @JsonValues = (
        SELECT [@Step]=1 FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
    );
    EXEC sp_api_modal_restart @values=@JsonValues;
    RETURN;
END

Complete Example: Pizza Order Form

This example demonstrates a multi-step form with proper variable management, state transitions, and user feedback.

DECLARE @Step INT;
DECLARE @Size NVARCHAR(20);
DECLARE @Toppings NVARCHAR(500);
DECLARE @DeliveryNote NVARCHAR(200);
DECLARE @NextButton NVARCHAR(100);
DECLARE @BackButton NVARCHAR(100);
DECLARE @Title NVARCHAR(MAX) = N'🍕 Epic Pizza Adventure 🍕';
DECLARE @Message NVARCHAR(MAX);
DECLARE @StepTitle NVARCHAR(MAX);
DECLARE @StepText NVARCHAR(MAX);
DECLARE @JsonValues NVARCHAR(MAX);
DECLARE @Placeholder NVARCHAR(200);

EXEC sp_api_modal_get_value @name='@Step', @value=@Step OUT;
EXEC sp_api_modal_get_value @name='@Size', @value=@Size OUT;
EXEC sp_api_modal_get_value @name='@Toppings', @value=@Toppings OUT;
EXEC sp_api_modal_get_value @name='@DeliveryNote', @value=@DeliveryNote OUT;

IF @Step IS NULL SET @Step = 1;

IF @Step = 1
BEGIN
    SET @StepTitle = @Title;
    SET @StepText = N'Choose your pizza size (choose wisely, your stomach is watching!)';
    SET @Placeholder = N'Small/Medium/ABSOLUTELY MASSIVE';
    
    EXEC sp_api_modal_text @text=@StepTitle, @class='h3';
    EXEC sp_api_modal_text @text=@StepText;
    EXEC sp_api_modal_input @name='@Size', @value=@Size OUT, @placeholder=@Placeholder;
    EXEC sp_api_modal_button @name='@NextButton', @value=N'To infinity... and beyond! ➡️', @valueout=@NextButton OUT;
    
    IF @NextButton IS NOT NULL AND @Size IS NOT NULL
    BEGIN
        SET @Message = CASE @Size
            WHEN N'Small' THEN N'Are you sure? You seem hungrier than that! 🤔'
            WHEN N'Medium' THEN N'Playing it safe, I see! 👍'
            WHEN N'ABSOLUTELY MASSIVE' THEN N'Now THAT''S what I''m talking about! 🦖'
            ELSE N'Size recorded (but you''re being creative with the options! 😅)'
        END;
        EXEC sp_api_toast @text=@Message, @class='btn-success';
        
        SET @JsonValues = (
            SELECT [@Step]=2, [@Size]=@Size 
            FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
        );
        EXEC sp_api_modal_restart @values=@JsonValues;
        RETURN;
    END
END

IF @Step = 2
BEGIN
    SET @StepTitle = @Title;
    SET @StepText = N'Time for toppings! (No pineapple judgment here... much 🍍)';
    SET @Placeholder = N'Go wild! Mix and match!';
    
    EXEC sp_api_modal_text @text=@StepTitle, @class='h3';
    EXEC sp_api_modal_text @text=@StepText;
    EXEC sp_api_modal_input @name='@Toppings', @value=@Toppings OUT, @type=N'textarea', @placeholder=@Placeholder;
    EXEC sp_api_modal_button @name='@BackButton', @value=N'⬅️ Oops, wrong size!', @valueout=@BackButton OUT;
    EXEC sp_api_modal_button @name='@NextButton', @value=N'These toppings are perfect! ➡️', @valueout=@NextButton OUT;
    
    IF @BackButton IS NOT NULL
    BEGIN
        SET @JsonValues = (
            SELECT [@Step]=1, [@Size]=@Size 
            FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
        );
        EXEC sp_api_modal_restart @values=@JsonValues;
        RETURN;
    END
    
    IF @NextButton IS NOT NULL AND @Toppings IS NOT NULL
    BEGIN
        SET @Message = CASE WHEN @Toppings LIKE N'%pineapple%' 
            THEN N'Brave choice with the pineapple! 🍍😅'
            ELSE N'Toppings locked in! Looking tasty! 🤤'
        END;
        EXEC sp_api_toast @text=@Message, @class='btn-success';
        
        SET @JsonValues = (
            SELECT [@Step]=3, [@Size]=@Size, [@Toppings]=@Toppings 
            FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
        );
        EXEC sp_api_modal_restart @values=@JsonValues;
        RETURN;
    END
END

IF @Step = 3
BEGIN
    SET @StepTitle = @Title;
    SET @StepText = N'Any special delivery instructions? (Wrong answers only! 😉)';
    SET @Placeholder = N'E.g., Please have delivery person yodel the theme from Rocky...';
    
    EXEC sp_api_modal_text @text=@StepTitle, @class='h3';
    EXEC sp_api_modal_text @text=@StepText;
    EXEC sp_api_modal_input @name='@DeliveryNote', @value=@DeliveryNote OUT, @type=N'textarea', @placeholder=@Placeholder;
    EXEC sp_api_modal_button @name='@BackButton', @value=N'⬅️ Those toppings were a mistake!', @valueout=@BackButton OUT;
    EXEC sp_api_modal_button @name='@NextButton', @value=N'Review this masterpiece! 🎨', @valueout=@NextButton OUT;
    
    IF @BackButton IS NOT NULL
    BEGIN
        SET @JsonValues = (
            SELECT [@Step]=2, [@Size]=@Size, [@Toppings]=@Toppings 
            FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
        );
        EXEC sp_api_modal_restart @values=@JsonValues;
        RETURN;
    END
    
    IF @NextButton IS NOT NULL
    BEGIN
        SET @JsonValues = (
            SELECT [@Step]=4, [@Size]=@Size, [@Toppings]=@Toppings, [@DeliveryNote]=@DeliveryNote
            FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
        );
        EXEC sp_api_modal_restart @values=@JsonValues;
        RETURN;
    END
END

IF @Step = 4
BEGIN
    SET @StepTitle = CONCAT(@Title, N' - Final Review');
    SET @StepText = N'🎯 Your Epic Pizza Order:';
    
    EXEC sp_api_modal_text @text=@StepTitle, @class='h3';
    EXEC sp_api_modal_text @text=@StepText, @class='h4 mt-3';
    
    SET @Message = CONCAT(N'Size: ', @Size, 
        CASE @Size 
            WHEN N'Small' THEN N' (Really? Just a small? 🤏)'
            WHEN N'Medium' THEN N' (Classic choice! 👌)'
            WHEN N'ABSOLUTELY MASSIVE' THEN N' (Now we''re talking! 🦕)'
            ELSE N' (Creative size choice! 🎨)'
        END);
    EXEC sp_api_modal_text @text=@Message;
    
    SET @Message = CONCAT(N'Toppings: ', @Toppings,
        CASE WHEN @Toppings LIKE N'%pineapple%' 
            THEN N' (You brave, brave soul! 🍍✨)'
            ELSE N' (Excellent choices! 🌟)'
        END);
    EXEC sp_api_modal_text @text=@Message;
    
    SET @Message = CONCAT(N'Delivery Instructions: ', @DeliveryNote,
        CASE WHEN LEN(@DeliveryNote) > 50 
            THEN N' (Our delivery person is practicing as we speak! 🎭)'
            ELSE N' (Keeping it simple, eh? 📦)'
        END);
    EXEC sp_api_modal_text @text=@Message;
    
    EXEC sp_api_modal_button @name='@BackButton', @value=N'⬅️ Wait, let me fix something!', @valueout=@BackButton OUT;
    EXEC sp_api_modal_button @name='@NextButton', @value=N'Yes! Make this pizza reality! 🎨', @valueout=@NextButton OUT;
    
    IF @BackButton IS NOT NULL
    BEGIN
        SET @JsonValues = (
            SELECT [@Step]=3, [@Size]=@Size, [@Toppings]=@Toppings, [@DeliveryNote]=@DeliveryNote
            FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
        );
        EXEC sp_api_modal_restart @values=@JsonValues;
        RETURN;
    END
    
    IF @NextButton IS NOT NULL
    BEGIN
        SET @Message = N'🎉 Order confirmed! Our pizza artists are crafting your masterpiece while practicing their delivery performance! 🎭🍕';
        EXEC sp_api_toast @text=@Message, @class='btn-success';
        EXEC sp_api_modal_clear;
        RETURN;
    END
END

Screenshot of realized app

See how this complete app is realized with only the few lines of code which we showed earlier!

Screenshot of the complete pizza ordering web app

Key Implementation Notes

  1. Variable Preparation

    • Declare all variables at start
    • Set values before sp_api calls
    • Use descriptive variable names

  2. State Management

    • JSON for state persistence
    • Clear step progression
    • Validation at each step

  3. User Experience

    • Consistent feedback
    • Clear navigation
    • Engaging messages
    • Final review step

  4. Error Prevention

    • Input validation
    • Step validation
    • Clean state transitions