After yesterday’s query, I know many of you are asking what more can be done to this query?
First off – notice that the language file is still a number and not at language name, my original idea was to show you how to translate that into a name but it is really one big case statement. Here is a lite version:
select Case v_MeteredProductRule.LanguageID
when ‘1029’ then ‘Czech’
when ‘1033’ then ‘English (United States)’
when ‘65535’ then ‘Any’
else ‘unknown’
end as ‘Language’
from v_MeteredProductRule
So instead I will move on to my phase 2 plan.
If I was to add in a case statement in to cover all languages the query would increase by over 175! Based on this table http://www.microsoft.com/globaldev/reference/winxp/xp-lcid.mspx
MICROSOFT WILL NOT SUPPORT THESE UPDATES!
Have said the prerequisite warning, keep in mind that MS might during service package or version upgrade might delete all objects from the db, therefore I recommend having a backup of all your custom queries.
A useful option is to use a SQL function particularly if it will be used by many SQL queries.
Using our case statement above as the base for the function, this is what we end up with.
CREATE function Lang (@LangID varchar(100))
returns varchar (255)
as
Begin
select @langID = Case @Langid
when ‘1029’ then ‘Czech’
when ‘1033’ then ‘English (United States)’
when ‘65535’ then ‘Any’
else ‘unknown’
end
Return @Langid
end
and our final query would look like this:
SELECT
MPR.ProductName AS ‘Name’,
MPR.OriginalFileName AS ‘Original File Name’,
MPR.FileName AS ‘File Name’,
MPR.FileVersion AS ‘Version’,
dbo.Lang(MPR.LanguageID) as ‘Language’,
MPR.SourceSite AS ‘Site’,
case MPR.ApplyToChildSites
when ‘1’ then ‘True’
else ‘False’
end as ‘Apply to Child Site’,
case MPR.Enabled
when ‘1’ then ‘True’
else ‘False’
end as ‘Enabled’,
MPR.SecurityKey as ‘Rule ID’
FROM
v_MeteredProductRule MPR