July Challenge Solution: Localizing Language/Region-Based Customer Settings

Here's the solution to the July Reader Challenge. (To read the full Challenge, "Localizing Language/Region-Based Customer Settings," go to InstantDoc ID 96137.)

Solution:
Tony can create an inline T-SQL table-valued function that takes the culture as a parameter and returns the relevant details. The inline table-valued function acts like a parameterized view. He can join it with other tables, views, or functions, or he can use it directly. The code to create the inline table-valued function is replicated in Web Listing 1.

The function gets the localized product description by using the subquery in the following SELECT list:

 (SELECT COALESCE( 
  MIN(CASE pdc.CultureID
    WHEN @CultureID 
    THEN pd.Description END),
  MIN(CASE pdc.CultureID 
    WHEN N'en' 
    THEN pd.Description END))
  FROM Production.ProductDescription
    AS pd
  JOIN Production.ProductModel
    ProductDescriptionCulture AS pdc
    ON pdc.ProductModelID =
      pm.ProductModelID
    AND pdc.ProductDescriptionID =
      pd.ProductDescriptionID
    WHERE pdc.CultureID 
      IN ( @CultureID, N'en' ) 

The subquery looks for a product description based on the specified culture or English when a culture isn't specified. It uses a COALESCE expression in the SELECT list to retrieve the localized description if present. The MIN aggregate function with searched CASE expression in the SELECT list ensures that only one row is returned for each product model.

The T-SQL code in Web Listing 2 shows some examples of how to use the table-valued function.