I have the following table named Fruits.
ID English Spanish German
1 Apple Applice Apple-
2 Orange -- --
If the program passes 1 and English, I have to return 'Apple'. How could I write the sql query for that? Thank you.
4 Answers
select
ID,
case @Lang
when 'English' then English
when 'Spanish' then Spanish
end as Name
from Fruits
where ID = @ID;
or, if you have more than one column to choose, you can use apply so you don't have to write multiple case statements
select
F.ID,
N.Name,
N.Name_Full
from Fruits as F
outer apply (values
('English', F.English, F.English_Full),
('Spanish', F.Spanish, F.Spanish_Full)
) as N(lang, Name, Name_Full)
where F.ID = @ID and N.lang = @lang
Comments
Fisrt you should normalize database, to support multilanguage, this means split the table in 2
tables:
Fruit(FruitID, GenericName, etc)Languages(LanguageID, LanguageName, etc)FruitTranslations(FruitID, LanguageID, LocalizedName)
then the query will be just a simple query to table FruitTranslations...
If you still want a query for this then you can use Dynamic SQL,
DECLARE @cmd VARCHAR(MAX)
SET @Cmd = 'SELECT ' + @Language +
' FROM Fruits WHERE ID = ''' + CONVERT(VARCHAR, @Id) + ''''
EXEC(@Cmd)
3 Comments
roman
I'd rather not use dynamic for this task, especially if @lang is entered by user
Luis LL
@ArabicProgrammer indeed it could be subject to sql injection, this could be avoided 1. if you check that
@Language is a valid column against sys.tables inner join sys.columns..., 2 check that the @Languague is just one word, 3. @ID is INT Anyway the main point of my answer was the normalization part.Try this one -
DECLARE
@Lang VARCHAR(10) = 'English'
, @ID INT = 1
DECLARE @SQL NVARCHAR(MAX)
SELECT @SQL = '
SELECT ' + @Lang + '
FROM dbo.Fruits
WHERE ID = ' + CAST(@ID AS VARCHAR(4))
PRINT @SQL
EXEC sys.sp_executesql @SQL
you can do that using stored procedures because in stored procedures you can use 'if else' condition. ex:
@ID as int,@Lang as varchar(50)
if @Lang = 'English'
begin
select ID,English from Fruits where ID = @ID;
end
else
if @Lang = 'Spanish'
begin
select ID,Spanish from Fruits where ID = @ID;
end
...
EnglishorSpanish, that's a good indicator that your table design is wrong. Things that you want to query on ought to be values that exist within rows of data - not in the names of columns. The table ought to beID, Language, FruitNameand have one row for eachLanguage, for eachID.