TSQL.APP Mandated Practices

A list of mandated practices that will be covered in this chapter:

• 1. TSQL.APP Object Access Rule
• 2. Variable Declaration and Handling
• 3. Stored Procedure Usage
• 4. State Management
• 5. Error Handling
• 6. Data Operations
• 7. Code Organization
• 8. Validation and Security

Every code example and pattern must adhere to these practices without exception.

1. TSQL.APP Object Access Rule

Key Concept

TSQL.APP combines objects from multiple databases into your project database by utilizing:

• Direct table references
• Synonyms
• Views
• Other database objects

For this reason, a single logical entity (e.g., "Customers") might be implemented as any type of database object. In the two-database architecture:

• Objects can exist directly in your project database (A_PROJ)
• Objects can be synonyms pointing to your source database (A)
• The implementation type might change based on configuration

The Rule

ALWAYS use sys.objects to check for object existence!

Why?

• A single logical entity could be:
  • A direct table in A_PROJ
  • A synonym pointing to a table in A
  • A view in A_PROJ
  • A synonym pointing to a view in A
  • Or change between these types based on configuration
• Checking specific object types (sys.tables, sys.views, etc.) will miss other valid implementations
• Only sys.objects provides complete object type coverage

Implementation

-- ✅ TSQL.APP Compliant:
IF EXISTS (
    SELECT 1 
    FROM sys.objects 
    WHERE name = 'Customers' 
    AND type IN ('U', 'SN', 'V')
)

-- ❌ INCORRECT - Will miss synonyms and views:
IF EXISTS (SELECT 1 FROM sys.tables WHERE name = 'Customers')

-- ❌ INCORRECT - Will miss tables and synonyms:
IF EXISTS (SELECT 1 FROM sys.views WHERE name = 'Customers')

2. Variable Declaration and Handling

TSQL.APP allows creation of highly interactive and context-aware modal actions. The created modals interact with the users of the solution. TSQL.APP offers full access via action code to the application database. TSQL.APP offers a set of pre declared variables holding actual contextual data for the user, like the name of the card being displayed in @card_name, the current record in @id, the set of selected records on the card in @IDS etc.

Is is important to know all the pre declared context-aware variables and when creating action code, it is important NOT to recreate variables with the same names, as the pre declared context aware variables always exist!

Mandatory Rules

1. USE THESE TSQL.APP CONTEXT VARIABLES

• @card_id nvarchar(32) -- id of the current card
• @id nvarchar(32) -- id of the current database record of the card (in form view) or cursor position (in list view)
• @ids nvarchar(max) -- selected record ids in listview of a card
• @basetable nvarchar(128) -- base table of the card (a card can be based on a table of a view)
• @tablename nvarchar(128) -- table name or view name used by the card (one database table or view can have multiple cards, like the table [companies] can have cards like [suppliers] and [customers])
• @identity_column nvarchar(1024) -- identity column of the table/view used by the card
• @parent_card_id nvarchar(32) -- cards can be related to other cards (following the database relations)
• @parent_id nvarchar(32) -- parent database record id
• @path nvarchar(4000) -- current path
• @previous_path nvarchar(4000)
• @is_new nvarchar(32)
• @user_id nvarchar(32) -- id in the meta database table api_user holding the logged-in user id
• @is_form nvarchar(32) -- indication of current card; 1 = card is in form modus; 0 = card is in list modus
• @hostname nvarchar(max) -- host name of the api (of the solution)
• @is_picklist nvarchar(32)
• @picklist_fieldname nvarchar(128)
• @picklist_type nvarchar(16)
• @card_field_id nvarchar(32) -- id of the selected card field (id in table: [api_card_fields])
• @card_name nvarchar(128) -- name of the current card (like [companies] or [suppliers])
• @parent_card_name nvarchar(128) -- name of the related parent card
• @parent_parent_id nvarchar(32) -- parent id of the parent
• @user_name nvarchar(256) -- current logged-in user name (this is always an email address like "rick@tsql.app")
• @user nvarchar(256) -- also current logged-in user name (this is always an email address like "rick@tsql.app")
• @uri nvarchar(4000)
• @selected_field_name nvarchar(128) -- name of the selected field on the current card (for use as contextual data in action scrips)
• @origin nvarchar(1024)
• @current_card_action_id int -- id of the current action script code in the meta database table [api_card_actions]

Avoid creating variables with these names, as they are already declared by TSQL.APP and always contain contextual user data.

2. ALL variables MUST be declared at the start of the script

   • No exceptions or late declarations
   • Group related variables together
   • Use clear, descriptive names

3. ALWAYS use Unicode

   • Prefix string literals with N
   • Use NVARCHAR for string variables
   • Consider NVARCHAR(MAX) for text that might be long

4. NEVER pass calculations directly to stored procedures

   • Prepare all values in variables first
   • One operation per variable
   • Clean, traceable data flow

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

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

3. Stored Procedure Usage

Mandatory Rules

1. NEVER create stored procedures in action code

   • Action code runs as temporary stored procedures
   • Use only framework and system procedures

2. ONLY use documented stored procedures

   • Check 00_tsql.app_framework_procs.csv for valid procedures
   • Verify all parameters before use
   • Never assume parameter names or existence

3. Parameter Passing Protocol

   • Only pass @variables or fixed values
   • Never pass expressions or functions
   • Prepare all values before the call

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

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

4. State Management

Required Practices

1. ALWAYS synchronize modal values

   • Use sp_api_modal_get_value for all interactive elements
   • Sync at the start of the script
   • Handle null cases appropriately

2. JSON State Transfer Protocol

   • Use JSON for multi-step processes
   • Include all required state variables
   • Follow the FOR JSON PATH pattern

-- ✅ Correct
DECLARE @ButtonValue NVARCHAR(MAX);
EXEC sp_api_modal_get_value @name='@ButtonValue', @value=@ButtonValue OUT;

-- ❌ Incorrect
IF @ButtonValue IS NOT NULL  -- Not synchronized with modal state

5. Error Handling

Mandatory Approach

1. ALWAYS use structured error handling

   • Implement TRY/CATCH blocks
   • Prepare error messages in variables
   • Use appropriate notification methods

2. User Feedback Protocol

   • Clear error messages
   • Appropriate warning classes
   • Validation feedback

-- ✅ Correct
BEGIN TRY
    -- Operation code
END TRY
BEGIN CATCH
    DECLARE @ErrorMsg NVARCHAR(MAX);
    SET @ErrorMsg = ERROR_MESSAGE();
    EXEC sp_api_modal_alert @alert_text = @ErrorMsg;
END CATCH

6. Data Operations

Required Patterns

1. Temporary Table Management

   • Clean up existing temporary tables
   • Use consistent naming conventions
   • Drop tables when done

2. Data Modification Protocol

   • Validate before modifying
   • Use transactions where appropriate
   • Handle concurrency issues

-- ✅ Correct
IF OBJECT_ID('tempdb..#Results') IS NOT NULL 
    DROP TABLE #Results;
CREATE TABLE #Results (...)

7. Code Organization

Mandatory Structure

1. Logical Grouping

   • Variables at top
   • Synchronization after declarations
   • Business logic in clear sections
   • Clean navigation flow

2. Documentation Requirements

   • Clear section comments
   • Purpose documentation
   • Validation rules explained

-- ✅ Correct Structure
-- 1. Variable declarations
DECLARE @Var1 ...
DECLARE @Var2 ...

-- 2. State synchronization
EXEC sp_api_modal_get_value...

-- 3. Business logic
IF @Condition...

8. Validation and Security

Required Practices

1. Input Validation Protocol

   • Validate all user inputs
   • Check data types and ranges
   • Sanitize string inputs

2. Security Guidelines

   • Handle sensitive data appropriately
   • Use proper escaping methods
   • Follow role-based access patterns

-- ✅ Correct
IF LEN(TRIM(@UserInput)) < 2
BEGIN
    SET @ErrorMsg = N'Input too short';
    EXEC sp_api_toast @text = @ErrorMsg;
    RETURN;
END

These mandated practices form the foundation for all TSQL.APP development. They ensure:

• Consistent code quality
• Reliable application behavior
• Maintainable codebase
• Secure operations
• Optimal performance

Every code example and pattern must adhere to these practices without exception.