Value Lists Using Stored Procedures

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

Using stored procedures is the best choice to have lists in multiple languages.

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

Samples of Value Lists Using Stored Procedures

Below you will find sample stored procedures for each supported database platform.

Use the suggested solutions to select data.

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

Also, the procedures show a simple way to translate list values to any language using the xls.translations table and the @data_language parameter.

Of course, you can use your translation solution or just a simple SELECT like

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

You can find more examples in Sample 02 - Advanced Features from SaveToDB SDK.

SQL Server | MySQL | PostgreSQL | Oracle | DB2 | NuoDB | Snowflake

Sample Value List Using Stored Procedure for Microsoft SQL Server

Note that SET NOCOUNT ON is required for 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 IBM DB2

--#SET TERMINATOR %%

CREATE PROCEDURE S02.XL_LIST_COMPANY_ID (
    DATA_LANGUAGE CHAR(2)
    )
    DYNAMIC RESULT SETS 1
    READS SQL DATA
    DETERMINISTIC
    CALLED ON NULL INPUT
    COMMIT ON RETURN NO
    LANGUAGE SQL
P1: BEGIN

DECLARE Cursor1 CURSOR WITH RETURN FOR
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;

OPEN Cursor1;

END P1
%%

--#SET TERMINATOR ;

Sample Value List Using Stored Procedure for NuoDB

CREATE PROCEDURE S02.XL_LIST_COMPANY_ID (
    DATA_LANGUAGE CHAR(2)
    )
RETURNS tmp_tab (
    ID INTEGER,
    NAME VARCHAR(50)
    )
AS

INSERT INTO tmp_tab
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_PROCEDURE
@@

Sample Value List Using Stored Procedure for Snowflake

Snowflake supports stored procedures written in JavaScript.

The usage scenario is the same.