SQL Server How to SELECT a column only if it exists in the table

Question

I would like to execute a SELECT, where it selects a column-value only if that column exists in the table, else display null.

This is what I'm currently doing:

SELECT TOP 10 CASE WHEN EXISTS
    (SELECT * FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE TABLE_SCHEMA ='test' and TABLE_NAME='tableName' and COLUMN_NAME='columnName') 
THEN columnName ELSE NULL END AS columnName

I also tried this:

SELECT TOP 10 CASE WHEN 
    (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE TABLE_SCHEMA ='test' and TABLE_NAME='tableName' and COLUMN_NAME='columnName') >0 
THEN columnName ELSE NULL END AS columnName

Both of them work well if the column is present in the table. But when the column is not present, it gives me the error :

Invalid column name 'columnName'

Answer 1

You can write as:

SELECT CASE WHEN EXISTS
(
    SELECT * FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE TABLE_SCHEMA ='test' and TABLE_NAME='tableName' 
    and COLUMN_NAME='columnName'
) 
THEN
(
    SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE TABLE_SCHEMA ='test' and TABLE_NAME='tableName'
    and COLUMN_NAME='columnName'
)
ELSE
NULL 
END
AS columnName

DEMO

Edit: If you are looking to select top 10 values from a table's column if that column exists then you need to write a dynamic query as:

SELECT @columnVariable =     
CASE WHEN EXISTS
(
    SELECT * FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_SCHEMA ='test' and TABLE_NAME='tableName' 
    and COLUMN_NAME='columnName'
) 
THEN
(
    SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_SCHEMA ='test' and TABLE_NAME='tableName' 
    and COLUMN_NAME='columnName'
)
ELSE
NULL 
END


/* Build the SQL string one time.*/
SET @SQLString =
     N'SELECT TOP 10 ' + @columnVariable+ '
       FROM test.tableName ';      


EXECUTE sp_executesql @SQLString

DEMO2