|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.)
Tony can create an inline 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:
THEN pd.Description END),
THEN pd.Description END))
ProductDescriptionCulture AS pdc
ON pdc.ProductModelID =
AND pdc.ProductDescriptionID =
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.