Select data from 2 columns to a specific column name based on an input variable in a procedure

Question

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!

Answer 1

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