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