Check if a column exists before adding the column to SQL table without altering table for each column

Question

I am trying to alter a table to add three new columns but I would like to check if the columns names before adding and if it already exists, just skip else add the column,

 ALTER TABLE TESTTABLE
 ADD [ABC] [int] ,
     [XYZ] [ [int] ,
     [PQR]  [int] 
GO

I have the below script

IF NOT EXISTS(
    SELECT *
    FROM sys.columns 
    WHERE Name      = N'ABC'
      AND Object_ID = Object_ID(N'TESTTABLE'))
BEGIN
    ALTER TABLE TESTTABLE
    ADD [ABC] int
END

but this has to be done for each column is there a better way to achieve this?

Answer 1

If you're sure these columns will always and only be added together at the same time, you can use IN to check for existence of any and then add them all if none exist:

IF NOT EXISTS(
    SELECT *
    FROM sys.columns 
    WHERE Name IN (N'ABC',N'XYZ',N'PQR')
      AND Object_ID = Object_ID(N'TESTTABLE'))
BEGIN
    ALTER TABLE TESTTABLE
    ADD [ABC] int,
        [XYZ] int,
        [PQR] int
END

Do note that this will not execute if any one of your columns already exists. If there's a chance of that happening, you'll need to make each check individually as you're already doing.