Downloads
96684.zip

Subscribe to SQL Server Magazine UPDATE, a free weekly email newsletter, with exclusive commentary by Brian Moran. Sign up now at http://www.sqlmag.com/email.

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.