Help with constructing a conditional SQL statement

Question

Hello,

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

Answer 1

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' 
        )
    )

Answer 2

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.

Answer 3

Another method:

Select Top 1 *
From   Entries
Where  Language In ('Swedish', 'English')
Order By Case When Language = 'Swedish' Then 1 Else 2 End

Answer 4

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

Answer 5

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 storage 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!

Answer 6

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 
)