如果SQL中不存在列，则为NULL [英] NULL if column doesn't exists in SQL

问题描述

我只想知道如果列不存在，是否可以将列设置为null。



我知道我可以执行以下操作：

 选择 
 Column1，
 < span class =code-keyword> NULL   as  Column2 
  FROM  < span class =code-keyword>表 

但我想要的是什么如果列不存在，则do为NULL列。

解决方案

您可以执行以下操作：

  SELECT  Column1，
  CASE   WHEN  存在（选择  null  来自 information_schema.columns  where  column_name = '  Column2' 和 table_name = '  TableName'）那么 Column2 
  ELSE   NULL   END   AS  Column2 
  FROM  TableName 

编辑：

由于列名不存在，上述查询将无法编译。您将需要动态SQL来执行此类查询。下面是一个非常基本的语法。

  DECLARE   @ query   nvarchar （max）= ' ' + '  SELECT' + 
（ CASE   WHEN   EXISTS （ SELECT   NULL   FROM  information_schema.columns  WHERE  column_name = '  Column2'  AND  table_name = '  Table2'）
 那么 '  Column2' 
  ELSE  '  NULL AS Column2'  END ）+ 
 ' < span class =code-string> FROM Table2' 
   -    PRINT @query  
  EXEC  sp_executesql  @ query  

您的查询将是......

 选择 Column1，ISNULL（Column2， NULL ） AS  Column2  FROM  表 

使用SQL中提供的ISNULL函数。

 选择 
 Column1，
 ISNULL（Column2， NULL ） AS    Column2' 
  FROM  表 

I just like to know if it is possible to set a column to null if column doesn't exists.

I know that I can do something like:

Select
Column1,
NULL as Column2
FROM Table

But what I want to do is NULL the column only if column doesn't exists.

解决方案

You can do something like this:

SELECT Column1,
        CASE WHEN exists (select null from information_schema.columns where column_name='Column2'and table_name='TableName') THEN Column2
        ELSE NULL END AS Column2
FROM TableName

EDIT:
The above query won't compile as the column name do not exist. You will need dynamic SQL to execute such queries. Below is a very basic syntax how you would do it.

DECLARE @query nvarchar(max) = '' + 'SELECT ' +
        (CASE WHEN EXISTS (SELECT NULL FROM information_schema.columns WHERE column_name='Column2' AND table_name='Table2')
            THEN 'Column2'
            ELSE 'NULL AS Column2' END) +
        ' FROM Table2'
--PRINT @query
EXEC sp_executesql @query

Your query will be......

Select Column1, ISNULL(Column2,NULL) AS Column2 FROM Table

Hi,

Use ISNULL function available in SQL.

Select
Column1,
ISNULL(Column2,NULL) AS 'Column2'
FROM Table

这篇关于如果SQL中不存在列，则为NULL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！