TSQL.APP Essential Training Patterns

Foundational Rules

1. Variable Declaration Rules

• ALL variables MUST be declared at the start of the script
• Use NVARCHAR(MAX) for text variables
• Always use N prefix for string literals
• Group related variables together
• Use clear, descriptive names

2. Parameter Passing Rules

• Only pass @variables or fixed values to stored procedures
• Never pass calculations or concatenations directly
• Always prepare parameter values in variables before executing procedure calls
• Never use functions directly in procedure calls

3. Procedure Usage Rules

• Never create stored procedures in action scripts
• Only use documented procedures from framework
• Always verify parameters in 00_tsql.app_framework_procs.csv
• Never assume parameter existence

Pattern 1: Basic User Input

Demonstrates proper variable handling and modal interaction.

-- 1. Variable declarations (ALL at start)
DECLARE @UserInput NVARCHAR(MAX);
DECLARE @SubmitButton NVARCHAR(MAX);
DECLARE @Message NVARCHAR(MAX);
DECLARE @Title NVARCHAR(MAX);
DECLARE @Placeholder NVARCHAR(100);
DECLARE @ButtonClass NVARCHAR(100);
DECLARE @ButtonValue NVARCHAR(100);

-- 2. Synchronize with modal state
EXEC sp_api_modal_get_value @name='@UserInput', @value=@UserInput OUT;
EXEC sp_api_modal_get_value @name='@SubmitButton', @value=@SubmitButton OUT;

-- 3. Prepare display values
SET @Title = N'Please enter your name:';
SET @Placeholder = N'Enter name here';
SET @ButtonValue = N'Submit';
SET @ButtonClass = N'btn-primary';

-- 4. Display title
EXEC sp_api_modal_text @text=@Title;

-- 5. Create input field
EXEC sp_api_modal_input 
    @name = '@UserInput',
    @value = @UserInput OUT,
    @placeholder = @Placeholder;

-- 6. Add submit button
EXEC sp_api_modal_button 
    @name = '@SubmitButton',
    @value = @ButtonValue,
    @valueout = @SubmitButton OUT,
    @class = @ButtonClass;

-- 7. Handle submission
IF @SubmitButton IS NOT NULL
BEGIN
    -- Validate input
    IF LEN(TRIM(ISNULL(@UserInput, N''))) < 2
    BEGIN
        SET @Message = N'Please enter a valid name';
        EXEC sp_api_toast @text=@Message, @class=N'btn-warning';
        RETURN;
    END

    -- Prepare success message
    SET @Message = CONCAT(N'Hello, ', @UserInput, N'!');
    
    -- Show success notification
    EXEC sp_api_toast @text=@Message, @class=N'btn-success';
    
    -- Clear modal
    EXEC sp_api_modal_clear;
END

Pattern 2: Data Display and Export

-- 1. Variable declarations (ALL at start)
DECLARE @Title NVARCHAR(MAX);
DECLARE @CloseButton NVARCHAR(MAX);
DECLARE @Message NVARCHAR(MAX);
DECLARE @ButtonClass NVARCHAR(100);
DECLARE @ButtonValue NVARCHAR(100);
DECLARE @ErrorMsg NVARCHAR(MAX);
DECLARE @DisplayText NVARCHAR(MAX);

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

-- 3. Begin error handling
BEGIN TRY
    -- 4. Prepare display values
    SET @Title = N'Sales Report by Department';
    SET @ButtonValue = N'Close';
    SET @ButtonClass = N'btn-secondary';
    SET @DisplayText = N'Sales report showing departmental performance with category breakdowns.';

    -- 5. Create original data table (simulating a real table/view)
    -- Note: In real application, this would be your actual table or view
    IF OBJECT_ID('tempdb..#OriginalSalesData') IS NOT NULL 
        DROP TABLE #OriginalSalesData;

    -- Pure data table without any special suffixes
    CREATE TABLE #OriginalSalesData (
        DepartmentName NVARCHAR(100),
        CategoryName NVARCHAR(100),
        ProductName NVARCHAR(100),
        QuantitySold INT,
        UnitPrice DECIMAL(10,2),
        TotalAmount DECIMAL(10,2),
        CurrentStatus NVARCHAR(20)
    );

    -- Insert sample data into original structure
    INSERT INTO #OriginalSalesData VALUES 
        -- Electronics
        (N'Electronics', N'Computers', N'Gaming Laptop', 5, 1299.99, 6499.95, N'In Stock'),
        (N'Electronics', N'Computers', N'Business Laptop', 8, 899.99, 7199.92, N'Low Stock'),
        (N'Electronics', N'Phones', N'Smartphone XL', 12, 799.99, 9599.88, N'In Stock'),
        (N'Electronics', N'Phones', N'Basic Phone', 15, 199.99, 2999.85, N'Clearance'),
        -- Home & Living
        (N'Home & Living', N'Furniture', N'Office Chair', 10, 199.99, 1999.90, N'In Stock'),
        (N'Home & Living', N'Furniture', N'Desk', 7, 299.99, 2099.93, N'In Stock'),
        (N'Home & Living', N'Appliances', N'Coffee Maker', 20, 79.99, 1599.80, N'Featured'),
        (N'Home & Living', N'Appliances', N'Toaster', 25, 49.99, 1249.75, N'In Stock');

    -- 6. Create display-formatted temporary table
    -- This follows the pattern from documentation where we SELECT INTO
    -- a new table with specially formatted column names
    IF OBJECT_ID('tempdb..#DisplaySalesReport') IS NOT NULL 
        DROP TABLE #DisplaySalesReport;

    -- SELECT INTO with special column suffixes for display
    SELECT 
        -- Grouping columns for hierarchy
        [Department*] = DepartmentName,        -- * enables grouping
        [Category*] = CategoryName,            -- nested grouping
        
        -- Regular display columns
        [Product] = ProductName,               -- no special handling
        
        -- Aggregation columns
        [Quantity@] = QuantitySold,           -- @ enables totaling
        
        -- Columns with both aggregation and styling
        [Unit Price@~text-success] = UnitPrice,    -- totaling with green text
        [Total Sales@~text-primary] = TotalAmount, -- totaling with themed color
        
        -- Styled status column
        [Status~text-light] = CurrentStatus         -- light text styling
    INTO #DisplaySalesReport
    FROM #OriginalSalesData;

    -- 7. Display title
    EXEC sp_api_modal_text @text=@Title, @class=N'h3';

    -- 8. Add descriptive text
    EXEC sp_api_modal_text @text=@DisplayText, @class=N'text-muted';

    -- 9. Display formatted table with grouping and export options
    EXEC sp_api_modal_table 
        @tmptable = N'#DisplaySalesReport',    -- Using our formatted display table
        @print = 1,
        @excel = 1,
        @orderby = N'ORDER BY [Department*], [Category*], Product';

    -- 10. Add close button
    EXEC sp_api_modal_button 
        @name = '@CloseButton',
        @value = @ButtonValue,
        @valueout = @CloseButton OUT,
        @class = @ButtonClass;

    -- 11. Handle close action
    IF @CloseButton IS NOT NULL
    BEGIN
        SET @Message = N'Report closed';
        EXEC sp_api_toast @text=@Message, @class=N'btn-info';
        EXEC sp_api_modal_clear;
        RETURN;
    END

END TRY
BEGIN CATCH
    -- Error handling as per mandated practices
    SET @ErrorMsg = ERROR_MESSAGE();
    EXEC sp_api_toast @text=@ErrorMsg, @class=N'btn-danger';
    RETURN;
END CATCH

-- 12. Clean up (always at the end, after error handling)
IF OBJECT_ID('tempdb..#OriginalSalesData') IS NOT NULL 
    DROP TABLE #OriginalSalesData;
IF OBJECT_ID('tempdb..#DisplaySalesReport') IS NOT NULL 
    DROP TABLE #DisplaySalesReport;

Pattern 3: Multi-Step Form

-- 1. Variable declarations
DECLARE @Step INT;
DECLARE @Name NVARCHAR(100);
DECLARE @Email NVARCHAR(100);
DECLARE @NextButton NVARCHAR(100);
DECLARE @BackButton NVARCHAR(100);
DECLARE @Message NVARCHAR(MAX);
DECLARE @JsonValues NVARCHAR(MAX);
DECLARE @StepTitle NVARCHAR(MAX);
DECLARE @DisplayText NVARCHAR(MAX);
DECLARE @Placeholder NVARCHAR(100);
DECLARE @ButtonClass NVARCHAR(100);
DECLARE @ButtonValue NVARCHAR(100);

-- 2. Synchronize with modal state
EXEC sp_api_modal_get_value @name='@Step', @value=@Step OUT;
EXEC sp_api_modal_get_value @name='@Name', @value=@Name OUT;
EXEC sp_api_modal_get_value @name='@Email', @value=@Email OUT;

-- 3. Initialize step if needed
IF @Step IS NULL SET @Step = 1;

-- Step 1: Name Input
IF @Step = 1
BEGIN
    -- Prepare variables
    SET @StepTitle = N'Step 1: Enter Your Name';
    SET @Placeholder = N'Full name';
    SET @ButtonValue = N'Next →';
    SET @ButtonClass = N'btn-primary';

    -- Display step title
    EXEC sp_api_modal_text @text=@StepTitle, @class=N'h3';
    
    -- Input field
    EXEC sp_api_modal_input 
        @name = '@Name', 
        @value = @Name OUT, 
        @placeholder = @Placeholder;
    
    -- Next button
    EXEC sp_api_modal_button 
        @name = '@NextButton', 
        @value = @ButtonValue, 
        @valueout = @NextButton OUT,
        @class = @ButtonClass;

    IF @NextButton IS NOT NULL
    BEGIN
        -- Validate input
        IF LEN(TRIM(ISNULL(@Name, N''))) < 2
        BEGIN
            SET @Message = N'Please enter a valid name (at least 2 characters)';
            EXEC sp_api_toast @text=@Message, @class=N'btn-warning';
            RETURN;
        END

        -- Prepare state for next step
        SET @JsonValues = (
            SELECT [@Step]=2, [@Name]=@Name 
            FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
        );
        
        -- Move to next step
        EXEC sp_api_modal_restart @values=@JsonValues;
        RETURN;
    END
END

-- Step 2: Email Input
IF @Step = 2
BEGIN
    -- Prepare display text
    SET @StepTitle = N'Step 2: Enter Your Email';
    SET @DisplayText = CONCAT(N'Current name: ', ISNULL(@Name, N''));
    SET @Placeholder = N'email@example.com';

    -- Display step information
    EXEC sp_api_modal_text @text=@StepTitle, @class=N'h3';
    EXEC sp_api_modal_text @text=@DisplayText, @class=N'text-muted';
    
    -- Input field
    EXEC sp_api_modal_input 
        @name = '@Email', 
        @value = @Email OUT, 
        @placeholder = @Placeholder;
    
    -- Prepare back button
    SET @ButtonValue = N'← Back';
    SET @ButtonClass = N'btn-secondary';
    
    -- Back button
    EXEC sp_api_modal_button 
        @name = '@BackButton', 
        @value = @ButtonValue, 
        @valueout = @BackButton OUT,
        @class = @ButtonClass,
        @inline = 1;
    
    -- Prepare submit button    
    SET @ButtonValue = N'Submit';
    SET @ButtonClass = N'btn-success';
    
    -- Submit button
    EXEC sp_api_modal_button 
        @name = '@NextButton', 
        @value = @ButtonValue, 
        @valueout = @NextButton OUT,
        @class = @ButtonClass,
        @inline = 1;

    -- Handle back navigation
    IF @BackButton IS NOT NULL
    BEGIN
        SET @JsonValues = (
            SELECT [@Step]=1, [@Name]=@Name 
            FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
        );
        EXEC sp_api_modal_restart @values=@JsonValues;
        RETURN;
    END

    -- Handle submission
    IF @NextButton IS NOT NULL
    BEGIN
        -- Validate email
        IF @Email IS NULL OR @Email NOT LIKE '%_@_%._%'
        BEGIN
            SET @Message = N'Please enter a valid email address';
            EXEC sp_api_toast @text=@Message, @class=N'btn-warning';
            RETURN;
        END

        -- Prepare completion message
        SET @Message = CONCAT(N'Thank you, ', ISNULL(@Name, N'User'), N'! Your registration is complete.');
        
        -- Show success and clear
        EXEC sp_api_toast @text=@Message, @class=N'btn-success';
        EXEC sp_api_modal_clear;
        RETURN;
    END
END

Common Anti-Patterns to Avoid

1. Direct String Concatenation

-- ❌ Wrong
EXEC sp_api_modal_text @text = 'Hello ' + @Name;

-- ✅ Correct
DECLARE @DisplayText NVARCHAR(MAX);
SET @DisplayText = CONCAT(N'Hello ', @Name);
EXEC sp_api_modal_text @text = @DisplayText;

2. Direct Function Calls

-- ❌ Wrong
EXEC sp_api_modal_text @text = CAST(@Value AS NVARCHAR(MAX));

-- ✅ Correct
DECLARE @DisplayText NVARCHAR(MAX);
SET @DisplayText = CAST(@Value AS NVARCHAR(MAX));
EXEC sp_api_modal_text @text = @DisplayText;

3. Missing Null Checks

-- ❌ Wrong
IF LEN(@Input) < 2

-- ✅ Correct
IF LEN(TRIM(ISNULL(@Input, N''))) < 2

Best Practices Checklist

1. Variable Declaration

   • All variables declared at start
   • NVARCHAR(MAX) used for text
   • N prefix used for strings
   • Clear variable names

2. Parameter Passing

   • Only variables or fixed values passed
   • No direct calculations
   • Values prepared before calls
   • Proper parameter verification

3. Error Handling

   • Proper NULL checks
   • Input validation
   • Clear error messages
   • Proper toast notifications

4. State Management

   • Modal values synchronized
   • Clean JSON state handling
   • Proper navigation flow
   • Clear modal when done

5. Code Organization

   • Logical grouping
   • Clear comments
   • Consistent structure
   • Clean navigation