How to refactor IF statement with CASE WHEN

Question

Hi Community,

I would like to refactor the following with a CASE WHEN statement

IF NOT EXISTS ( SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'airports' AND COLUMN_NAME = 'airport_region') SELECT * FROM airports;

Can someone help with that?

Thanks

Answer 1

You are checking meta data before your SELECT statement.

Not much you can refactor unless you are changing your requirements.

Answer 2

Hi @Carlton Patterson

I was just wondering if the code could be re-written with CASE WHEN statement?

I will say no.

Basically, the IF/ELSE statements and CASE expressions are two fundamentally different parts of the language and are not used interchangeably.

IF/ELSE is for control-flow within a batch or stored-procedure.

CASE is for "choosing" a data-value within a SELECT or similar operation. It is basically like a function with "if logic" built in, a more powerful version of ISNULL() or COALESCE(). Check this simple example.

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

You mean something like this:

SELECT *,
CASE 
   WHEN EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'airports' AND COLUMN_NAME = 'airport_region') 
   THEN 'Column Exists' 
   ELSE 'Column Not Found' 
END as 'Check_Column_Exist' 
FROM airports;

Or use a CASE WHEN in case you want to filter the rows only when the column exists:

SELECT *
FROM airports
WHERE 
CASE 
   WHEN EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'airports' AND COLUMN_NAME = 'airport_region')
   THEN 1 ELSE 0 
END = 1;

Answer 4

Hi @Carlton Patterson

If you want to rewrite this query, there is no need to use CASE WHEN, just add a WHERE clause will work.

Check this:

SELECT * FROM airports 
WHERE NOT EXISTS 
(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'airports' AND COLUMN_NAME = 'airport_region')

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.