Value Lists Using SQL

You can use SELECT and EXEC commands to select parameter values, validation lists, and selection lists.

Here is a typical configuration in the xls.handlers table.

Specify the CODE handler type in the HANDLER_TYPE column and an SQL code in HANDLER_CODE.

In the simplest case, an SQL code contains a SELECT command like

SELECT id, name FROM s02.companies ORDER BY name, id

You can also use parameters. For example, to select values in a user's language like

SELECT
    c.id
    , COALESCE(t.TRANSLATED_NAME, c.name) AS name
FROM
    s02.companies c
    LEFT OUTER JOIN s02.xl_translations t ON t.TABLE_SCHEMA = 's02' AND t.TABLE_NAME = 'strings'
            AND t.LANGUAGE_NAME = @data_language AND t.COLUMN_NAME = c.name
ORDER BY
    name
    , id

The code above uses the context @data_language parameter populated with a user's data language.

Note that SQL Server, SQL Server Compact, and SQLite use parameters with the @ character as a prefix while other database platforms use the colon like :data_language.

Useful Tips

The sample configuration above contains the same code in two rows. In real applications, the code can be duplicated in a lot of rows.

You have two solutions to improve the case:

1. Create and use a stored procedure instead of the code.
2. Define an SQL-code object in the xls.objects table and use it in the xls.handlers table.

Here is a sample xls.objects configuration for the second way:

In this case, you can use the defined s02.xl_list_company_id_code object without the code definition: