Why can't I use a CASE statement to see if a column exists and not SELECT from it?

Question

Why does something like this not work?

SELECT
CASE 
WHEN NULLIF(COL_LENGTH('Customers', 'Somecol'), '') IS NULL THEN NULL
ELSE Somecol
END AS MyTest
FROM Customers;

I am just checking if the column exists, however, SQL Server complains about Somecol not existing. Is there an alternative to this in a single statement?

Answer 1

The following query uses the same idea as in this amazing answer by ypercube:

SELECT x.*
FROM (SELECT NULL AS SomeCol) AS dummy
CROSS APPLY
(
  SELECT
    ID,
    SomeCol AS MyTest
  FROM dbo.Customers
) AS x;

It works like this:

• if dbo.Customers has a column named SomeCol, then SomeCol in SomeCol AS MyTest will resolve as dbo.Customers.SomeCol;

• if the table has no such column, the reference will still be valid, because now it will be resolved as dummy.SomeCol: dummy columns can be referenced in that context.

You can specify multiple "spare" columns that way. The trick is not to use the table alias for such columns (which is a frowned-upon practice in most situations, but in this case omitting the table alias helps you to resolve the issue).

If the table is used in a join and the other table has its own SomeCol, you will probably need to use the above query as a derived table before using it in the join in order to keep the trick working, something like this:

SELECT ...
FROM
(
  SELECT x.*
  FROM (SELECT NULL AS SomeCol) AS dummy
  CROSS APPLY (
    SELECT
      ID,
      SomeCol AS MyTest
    FROM dbo.Customers
  ) AS x
) AS cust
INNER JOIN ...
;

Answer 2

One way to do this is to check for the columns existence, then build the Dynamic SQL based on whether that column exists or not.

Without Dynamic SQL, SQL Server will attempt to evaluate whether or not the column exists before it even executes the statment, resulting in an error.

It does, however, mean you will have 2 queries to write and potentially alter in future. But I don't believe you should really be targeting SELECT statements on columns that may not exist.

declare @SQL varchar(max)

If exists (select 1 from sys.columns where Name = N'NameOfColumn' and object_id=object_id(N'yourTableName'))
begin
set @SQL = 'select ID, NameOfColumn from yourTableName'
exec(@sql)
end
else
begin
Print 'Column does not exist'
end

Answer 3

You can make use of some XML to query columns that might be in the table.

Build an XML from all columns per row in a cross apply and extract the value using the values() function.

In this query ID is known so get it from the table directly. Col1 and Col2 might be there or not so get them using the XML.

select T.ID,
       TX.X.value('(Col1/text())[1]', 'int') as Col1,
       TX.X.value('(Col2/text())[1]', 'int') as Col2
from T
  cross apply (select T.* for xml path(''), type) as TX(X)

SQL Fiddle

Answer 4

My approach differs only slightly from the others. I prefer to use the system for this and simply get a count because you can assign the column count to a variable at the top of a query and then choose to proceed or not based on that. The downside to that is…if you have the same column name in multiple tables, you are uncertain that the column exists in the table you are wanting to query. However, the technique works on particular tables as well, since you are only looking to get a count.

The 'trouble' with asking for it specifically is -- the trouble you are experiencing. In general, if a NULL value causes you issues…find another way to verify existence. This is one way of doing that without risking upsetting the server.

SELECT COUNT(*) FROM sys.columns WHERE sys.columns.name = 'FarmID'

Answer 5

If I understood it correctly...

You can use the query something like below and act accordingly based on the count... If the count is > 1 then it means you have the col in that table, and the count = 0 then you don't have that col in that table

SELECT count(*)
FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME IN ('Id')
AND TABLE_SCHEMA='dbo' and TABLE_NAME ='UserBase' ;