I need to modify a stored procedure to bring back a constant column name, but have it switch from an English language response to French and visa/versa based on the language code sent to it.
This is the current procedure which brings back both the English (StaffLanguageName) and French (StaffLanguageNameFR ) data in response :
SELECT StaffLanguageName
, StaffLanguageNameFR
, StaffLanguageCode
FROM dbo.lookup_staff_language
ORDER BY StaffLanguageID
I am passing a variable to the procedure called @varLanguage which will specify EN or FR depending on what is required by the user.
This is what I need the procedure to be like…
@varLanguage varchar(2)
SELECT StaffLanguageName
, StaffLanguageNameFR
, StaffLanguageCode
FROM dbo.lookup_staff_language
IF @varLanguage = EN then
StaffLanguage = StaffLanguageName
ELSE
StaffLanguage = StaffLanguageNameFR
ORDER BY StaffLanguageID
I know the above statement is NOT correct way to do it, but essentially what I want it to do is…
output the column “StaffLanguage” with the value from StaffLanguageName if @varLanguage = EN
OR
output the column “StaffLanguage” with the value from StaffLanguageNameFR if @varLanguage = FR
Any help would be greatly appreciated. Thanks in advance!
CodePudding user response:
You can do this easily with a case expression. Something like this.
SELECT StaffLanguageName
, StaffLanguageNameFR
, StaffLanguageCode
, YourNewColumn = case @varLanguage when 'EN' then StaffLanguage when 'FR' then StaffLanguageFR end
FROM dbo.lookup_staff_language