TSQL.APP Modal Select Table Guide

Overview

sp_api_modal_select_table is a core TSQL.APP procedure that creates an interactive dropdown selection component. It uses a temporary table as its data source and is designed for selecting records from a dataset.

Syntax

EXEC sp_api_modal_select_table 
    @tmptable = N'#TableName',          -- Name of temporary table containing data
    @name = N'@VariableName',           -- Name of the variable to store selection
    @value = @Variable OUT,             -- Variable to receive selected value
    [@class = N'CSS_Classes',]          -- Optional CSS classes
    [@placeholder = N'Select...']       -- Optional placeholder text
    [@orderby = N'ORDER BY clause']     -- Optional sorting

Required Table Structure

The temporary table MUST contain these columns:

• id (any type, typically INT/NVARCHAR) - The value to be returned when selected
• name (NVARCHAR) - The human-readable text shown to users

-- Example of correct table structure
CREATE TABLE #example (
    id INT,              -- Will be returned in @value
    name NVARCHAR(100)   -- Will be shown in dropdown
)

Basic Usage Pattern

-- 1. Declare variables
DECLARE @SelectedValue NVARCHAR(MAX);
DECLARE @JsonValues NVARCHAR(MAX);

-- 2. Get existing value if any
EXEC sp_api_modal_get_value @name=N'@SelectedValue', @value=@SelectedValue OUT;

-- 3. Prepare data table
SELECT 
    id = CustomerID,
    name = CompanyName
INTO #customers 
FROM Customers
WHERE Active = 1;

-- 4. Create select component
EXEC sp_api_modal_select_table 
    @tmptable = N'#customers',
    @name = N'@SelectedValue',
    @value = @SelectedValue OUT,
    @placeholder = N'Choose a customer...';

-- 5. Handle selection
IF @SelectedValue IS NOT NULL
BEGIN
    -- Do something with the selection
END

Common Patterns

1. Concatenated Display Names

SELECT 
    id = OrderID,
    name = CONCAT_WS(N' - ',
        OrderDate,
        CustomerName,
        CAST(TotalAmount AS NVARCHAR(20))
    )
INTO #orders
FROM Orders

2. Formatted Display Names

SELECT 
    id = ProductID,
    name = CONCAT(
        ProductName,
        N' (',
        FORMAT(UnitPrice, 'C'),
        N')'
    )
INTO #products
FROM Products

3. Multi-Step Selection

-- Step 1: Select Category
SELECT 
    id = CategoryID,
    name = CategoryName
INTO #categories
FROM Categories;

EXEC sp_api_modal_select_table 
    @tmptable = N'#categories',
    @name = N'@CategoryID',
    @value = @CategoryID OUT;

-- Step 2: Show products for selected category
IF @CategoryID IS NOT NULL
BEGIN
    SELECT 
        id = ProductID,
        name = ProductName
    INTO #categoryProducts
    FROM Products
    WHERE CategoryID = @CategoryID;

    EXEC sp_api_modal_select_table 
        @tmptable = N'#categoryProducts',
        @name = N'@ProductID',
        @value = @ProductID OUT;
END

Best Practices

1. Variable Management

-- Always declare variables at start
DECLARE @SelectedID NVARCHAR(MAX);
DECLARE @JsonState NVARCHAR(MAX);

-- Get existing value
EXEC sp_api_modal_get_value @name=N'@SelectedID', @value=@SelectedID OUT;

2. Error Handling

BEGIN TRY
    -- Create temporary table
    SELECT id = ID, name = Name 
    INTO #data 
    FROM SourceTable;

    -- Handle empty dataset
    IF NOT EXISTS (SELECT 1 FROM #data)
    BEGIN
        EXEC sp_api_toast 
            @text = N'No records available',
            @class = N'btn-warning';
        RETURN;
    END

    EXEC sp_api_modal_select_table 
        @tmptable = N'#data',
        @name = N'@SelectedID',
        @value = @SelectedID OUT;

END TRY
BEGIN CATCH
    DECLARE @ErrorMsg NVARCHAR(MAX) = ERROR_MESSAGE();
    EXEC sp_api_toast @text=@ErrorMsg, @class=N'btn-danger';
END CATCH

3. Validation

-- Validate selection
IF @SelectedID IS NULL
BEGIN
    EXEC sp_api_toast 
        @text = N'Please select a record',
        @class = N'btn-warning';
    RETURN;
END

-- Verify selection still exists
IF NOT EXISTS (
    SELECT 1 
    FROM SourceTable 
    WHERE ID = @SelectedID
)
BEGIN
    EXEC sp_api_toast 
        @text = N'Selected record no longer exists',
        @class = N'btn-warning';
    RETURN;
END

Common Issues and Solutions

1. Selection Not Working

• Verify table has correct column names (id and name)
• Check column data types
• Ensure temporary table exists

2. Empty Dropdown

• Verify source data exists
• Check WHERE clauses
• Validate table creation

3. Formatting Issues

• Use N prefix for Unicode strings
• Use proper CONCAT or CONCAT_WS
• Handle NULL values in concatenation

Performance Tips

1. Index Temporary Tables for Large Datasets

SELECT id = ID, name = Name 
INTO #data 
FROM SourceTable;

CREATE INDEX IX_data_name ON #data(name);

2. Limit Dataset Size

SELECT TOP 100 
    id = ID, 
    name = Name 
INTO #data 
FROM SourceTable
ORDER BY Name;

3. Use Efficient Name Construction

-- Good: Efficient concatenation
SELECT 
    id = ID,
    name = CONCAT_WS(N' - ', Field1, Field2)
INTO #data
FROM SourceTable;

-- Avoid: Inefficient string manipulation
SELECT 
    id = ID,
    name = Field1 + N' - ' + ISNULL(Field2, N'')
INTO #data
FROM SourceTable;

Security Considerations

1. Validate all inputs before use
2. Consider row-level security if needed
3. Filter sensitive data from name field
4. Use parameterized queries
5. Implement proper error handling

Example Full Implementation

-- 1. Variable declarations
DECLARE @SelectedCustomerID NVARCHAR(MAX);
DECLARE @ErrorMsg NVARCHAR(MAX);

BEGIN TRY
    -- 2. Get current state
    EXEC sp_api_modal_get_value 
        @name=N'@SelectedCustomerID', 
        @value=@SelectedCustomerID OUT;

    -- 3. Prepare data
    SELECT TOP 100
        id = CustomerID,
        name = CONCAT_WS(N' - ',
            CompanyName,
            City,
            Country
        )
    INTO #customers
    FROM Customers
    WHERE Active = 1
    ORDER BY CompanyName;

    -- 4. Validate data exists
    IF NOT EXISTS (SELECT 1 FROM #customers)
    BEGIN
        EXEC sp_api_toast 
            @text=N'No active customers found',
            @class=N'btn-warning';
        RETURN;
    END

    -- 5. Create select component
    EXEC sp_api_modal_select_table 
        @tmptable = N'#customers',
        @name = N'@SelectedCustomerID',
        @value = @SelectedCustomerID OUT,
        @placeholder = N'Select a customer...',
        @orderby = N'ORDER BY name';

    -- 6. Handle selection
    IF @SelectedCustomerID IS NOT NULL
    BEGIN
        -- Process selection
        EXEC sp_api_toast 
            @text=CONCAT(N'Selected customer ID: ', @SelectedCustomerID),
            @class=N'btn-success';
    END

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