| 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:
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.
