I want to construct a SELECT statement with a conditional IF.
Like, IF there is no records with the language code 'Swedish':
SELECT * FROM Entries WHERE Language = 'Swedish'
THEN use 'English'
SELECT * FROM Entries WHERE Language = 'English'
How would I construct this statement using MSSQL?
Thanks,
Stefan
Naively:
SELECT *
FROM Entries
WHERE Language = 'Swedish'
UNION ALL
SELECT *
FROM Entries
WHERE Language = 'English'
AND NOT EXISTS (
SELECT *
FROM Entries
WHERE Language = 'Swedish'
)
or:
SELECT *
FROM Entries
WHERE Language = 'Swedish'
OR (Language = 'English'
AND NOT EXISTS (
SELECT *
FROM Entries
WHERE Language = 'Swedish'
)
)
Another method:
Select Top 1 *
From Entries
Where Language In ('Swedish', 'English')
Order By Case When Language = 'Swedish' Then 1 Else 2 End
you can write a stored procedure for this and use it from your code, something like
select count(*) into V from entries where language='Swedish'
IF (v>0)
// use swedish
else
// use english
see this example
hopefully this helps.
count(*) into V and IF (v>0) are not valid in tsql. Also a plain IF EIXSTS (SELECT 1 FROM from entries where language='Swedish') will be much faster than actually counting them with a COUNT(*) into a variable and then doing an IF on that variable.There are many ways to do this if you want to just setup a basic statement here is a good one.
IF (SELECT count(*) FROM entries WHERE language = 'english') > 0
BEGIN
//What you want to do for english
END
ELSE IF (SELECT count(*) FROM entries WHERE language = 'swedish') > 0
BEGIN
// What you want to do for Swedish
END
ELSE
BEGIN
// There are no records for those languages!!
END
If you want to use it as a stored procedure can try the following:
CREATE PROCEDURE GetLanguageRows
@language varchar(500)
AS
IF (SELECT count(*) FROM entries WHERE language = @language) > 0
BEGIN
//What you want to do for that language
END
ELSE
BEGIN
// No records found!
END
Now you can just use
exec GetLanguageRows 'English'
Hopefully I helped a little alongside those other great answers above!
If Exists(Select 1 From Entries Where Language = 'Swedish') Then Begin SELECT * FROM Entries WHERE Language = 'Swedish' End Else Begin Select * From Entries Where Language = 'Language' End
SELECT * FROM Entries AS e WHERE Language IN( 'Swedish','English')
AND NOT EXISTS(
SELECT * FROM Entries AS e1 WHERE Language IN( 'Swedish','English')
AND e.Language > e1.Language
)
