Simple Localization in SQL Server with a Formula field

This little trick works for an application supporting a few languages (English, Spanish, French), although it won't scale much further.

A SQL Server database table MyTable requires a Description field, but an English, Spanish, and French translation need to be stored in the record and (hopefully) retrieved transparently.  Three fields are created: EN_Description, SP_Description, FR_Description.  A fourth field Language holds one of three values ('EN','SP','FR').

A user-defined function is created:

CREATE FUNCTION dbo.udf_LanguageDependantValue
    (
    @language varchar(2),
             @EN_value varchar(100),
             @SP_value varchar(100),
             @FR_value varchar(100)
    )
RETURNS varchar(100)
AS
BEGIN
    DECLARE @result varchar(100)

    SET @result = 'Unassigned'
   
    IF @language = 'EN'
        SET @result = @EN_value
    IF @language = 'SP'
        SET @result = @SP_value
    IF @language = 'FR'
        SET @result = @FR_value

    RETURN @result
END

Back in the table definition, a final field Description is created, with the Formula:

([dbo].[udf_LanguageDependantValue]([Language], [EN_Description], [SP_Description], [FR_Description]))

Now use a standard SELECT statement and SQL Server automatically retrieves the correct language field:

SELECT Description FROM MyTable