User Context Variables in TSQL.APP

Overview

TSQL.APP provides a robust system for managing user-specific variables in metadata storage. These context variables persist permanently for each user until explicitly reset or modified, offering a reliable way to maintain user preferences and settings across sessions.

Core Functions

Setting Context Variables

EXEC sp_api_modal_set 
    @key='preference_name',     -- Unique identifier for the setting
    @value='preference_value'   -- Value to store in metadata

Retrieving Context Variables

DECLARE @StoredValue NVARCHAR(MAX);
EXEC sp_api_modal_get 
    @key='preference_name',     -- Name of setting to retrieve
    @value=@StoredValue OUT    -- Variable to receive the value

Key Features

Permanent storage: Values persist in metadata until explicitly changed
User-specific storage: Each user maintains their own separate context
Immediate availability: Values can be accessed from any action script
Clean isolation: Different users' contexts never interfere with each other
No database tables required: Storage handled by framework metadata

Common Use Cases

Report Filters

-- Store selected filters (persists until changed)
EXEC sp_api_modal_set @key='filter_Quarter', @value='4';
EXEC sp_api_modal_set @key='filter_Year', @value='2024';

-- Retrieve filters in any future session
DECLARE @Quarter NVARCHAR(MAX), @Year NVARCHAR(MAX);
EXEC sp_api_modal_get @key='filter_Quarter', @value=@Quarter OUT;
EXEC sp_api_modal_get @key='filter_Year', @value=@Year OUT;

User Preferences

-- Store display preference
EXEC sp_api_modal_set @key='records_per_page', @value='50';

-- Apply preference in any future list view
DECLARE @PageSize NVARCHAR(MAX);
EXEC sp_api_modal_get @key='records_per_page', @value=@PageSize OUT;

Best Practices

Use consistent naming conventions for context variables
- Prefix keys with their purpose (e.g., 'filter_', 'pref_', 'setting_')
- Use descriptive names that indicate the stored value
- Consider documenting your naming scheme for team reference
Manage variable lifecycle
- Reset values when they should no longer apply
- Update values when business rules change
- Regularly review stored preferences for relevance
Handle missing or outdated values
- Check for NULL returns when retrieving values
- Provide sensible defaults for first-time usage
- Consider version flagging for preference updates
Documentation and maintenance
- Document the context variables used in your application
- Maintain a list of valid values and their meanings
- Include cleanup procedures in application updates

Example Implementation

-- Complete example showing context variable usage
DECLARE @SelectedQuarter NVARCHAR(MAX);
DECLARE @SelectedYear NVARCHAR(MAX);
DECLARE @Message NVARCHAR(MAX);

-- Retrieve stored preferences (persists across sessions)
EXEC sp_api_modal_get @key='filter_Quarter', @value=@SelectedQuarter OUT;
EXEC sp_api_modal_get @key='filter_Year', @value=@SelectedYear OUT;

-- Apply defaults for first-time use
IF @SelectedQuarter IS NULL SET @SelectedQuarter = DATENAME(QUARTER, GETDATE());
IF @SelectedYear IS NULL SET @SelectedYear = DATENAME(YEAR, GETDATE());

-- Store new values (will persist until changed)
EXEC sp_api_modal_set @key='filter_Quarter', @value=@SelectedQuarter;
EXEC sp_api_modal_set @key='filter_Year', @value=@SelectedYear;

-- Confirm storage
SET @Message = CONCAT(N'Filters set for Q', @SelectedQuarter, N' ', @SelectedYear);
EXEC sp_api_toast @text=@Message, @class='btn-success';

Maintenance and Cleanup

To reset a stored value:

-- Clear a specific preference
EXEC sp_api_modal_set @key='filter_Quarter', @value=NULL;

-- Or set a new value
EXEC sp_api_modal_set @key='filter_Quarter', @value='1';