Value Lists Using Stored Procedures

You can leverage stored procedures to select parameter values, validation lists, and selection lists.

Using stored procedures is the preferred method for creating lists in multiple languages.

Here's a typical configuration for stored procedures in the xls.handlers table.

Samples of Value Lists Using Stored Procedures

Below are sample stored procedures for each supported database platform.

Use these examples to select data.

All procedures retrieve the id and name columns from the s02.companies table.

They also demonstrate a straightforward method to translate list values into any language using the xls.translations table and the @data_language parameter.

Of course, you can implement your own translation solution or simply use a basic SELECT statement like:

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

For more examples, check Sample 02 - Advanced Features in the SaveToDB SDK.

SQL Server | MySQL | PostgreSQL | Oracle | Snowflake

Sample Value List Using Stored Procedure for Microsoft SQL Server

Make sure to include SET NOCOUNT ON in SQL Server stored procedures to select data in Microsoft Excel.

CREATE PROCEDURE [s02].[xl_list_company_id]
    @data_language char(2) = NULL
AS
BEGIN

SET NOCOUNT ON

SELECT
    c.id,
    COALESCE(t.TRANSLATED_NAME, c.name) AS name
FROM
    s02.companies c
    LEFT OUTER JOIN xls.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

END

Sample Value List Using Stored Procedure for MySQL

CREATE PROCEDURE s02.xl_list_company_id (
    data_language char(2)
)
BEGIN

SELECT
    c.id,
    COALESCE(t.TRANSLATED_NAME, c.name) AS name
FROM
    s02.companies c
    LEFT OUTER JOIN xls.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;

END
//

Sample Value List Using Stored Procedure for PostgreSQL

CREATE OR REPLACE FUNCTION s02.xl_list_company_id (
    data_language varchar(2)
)
RETURNS table (
    id integer,
    name varchar
)
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
BEGIN

RETURN QUERY
SELECT
    c.id,
    COALESCE(t.TRANSLATED_NAME, c.name) AS name
FROM
    s02.companies c
    LEFT OUTER JOIN xls.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 NULLS FIRST,
    id NULLS FIRST;

END
$$;

Sample Value List Using Stored Procedure for Oracle Database

CREATE PROCEDURE S02.XL_LIST_COMPANY_ID (
    DATA_LANGUAGE CHAR,
    DATA OUT SYS_REFCURSOR
)
AS
BEGIN

OPEN DATA FOR
SELECT
    c.ID,
    COALESCE(t.TRANSLATED_NAME, N'' || c.NAME) AS NAME
FROM
    S02.COMPANIES c
    LEFT OUTER JOIN XLS.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;

END;
/

Sample Value List Using Stored Procedure for Snowflake

Snowflake supports stored procedures written in JavaScript, and the usage scenario remains the same.