Translating Data in SQL Queries

Stored procedures, SQL queries, OData FunctionImports, and REST APIs can use the @DataLanguage or @data_language parameter to select data in the user's preferred language.

Here's a sample SQL Server procedure that retrieves a list of companies:

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

The @DataLanguage and @data_language parameters are context-specific.

• SaveToDB and DBEdit pass the data language selected in the Options dialog box.
• DBGate extracts the language value from the URL.

For example, you can test these URLs with DBGate:

• https://dbgate.savetodb.com/edit/mssql-023/en-us/s02/usp_cashbook2
• https://dbgate.savetodb.com/edit/mssql-023/es-es/s02/usp_cashbook2
• https://dbgate.savetodb.com/edit/mssql-023/fr-fr/s02/usp_cashbook2