SQL Server 如何仅选择表中存在的列

Question

我想执行一个 SELECT，仅当该列存在于表中时才选择列值，否则显示 null。

这就是我目前正在做的事情:

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

我也尝试过这个:

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

如果表中存在该列，则它们都可以正常工作。但是当该列不存在时，它会给我错误:

列名“columnName”无效

Answer 1

你可以写成:

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

编辑:如果您希望从表的列中选择前 10 个值(如果该列存在)，那么您需要将动态查询编写为:

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