current community

your communities

more stack exchange communities

Stack Exchange
tour help
Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 4 down vote favorite
1

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?

share|improve this question
2  
Do you have an example for why you would want to do this? I can't understand why you'd want to write a query that attempts to select from a column that might not exist. –  Mark Sinkinson yesterday
3  
SQL Server evaluates that your statement syntax is correct before it executes it. Therefore all the columns referenced must exist in the tables, even if they are wrapped within a CASE statement. –  Mark Sinkinson yesterday
    
@MarkSinkinson: Names are checked after the syntax, but yes, SQL Server does that prior to actually running the batch. –  Andriy M yesterday
1  
Selecting from INFORMATION_SCHEMA could work as a workaround. –  Brilliand yesterday
2  
@Brilliand sys.columns is way better IMHO. –  Aaron Bertrand♦ yesterday
show 1 more comment

5 Answers

up vote 13 down vote accepted

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 a 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 ...
;
share|improve this answer
    
Whoa (media.giphy.com/media/117P9MlBvxqSBi/giphy.gif) –  Carson Reinke yesterday
    
Mind = Blown. We are not worthy! –  Mark Sinkinson yesterday
1  
@AndriyM Excellent answer. Learned something new :-) –  Kin yesterday
    
I wonder if the SQL compiler is just a tiny bit complicated. Super cool what you can do. –  Max Vernon yesterday
add comment
up vote 8 down vote

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
share|improve this answer
4  
+1 from me .. as I was lacking caffeine :-) –  Kin yesterday
    
Yes, makes sense, however, must be in a single statement. Ultimately, I am looking for probably some magic system function that does not exist. –  Carson Reinke yesterday
add comment
up vote 4 down vote

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

share|improve this answer
add comment
up vote 0 down vote

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'
share|improve this answer
1  
Why not use the sysobjects as well in your query to check whether the specific table has such a column? –  ypercube yesterday
    
Yes…I mentioned that could be done…you could even do the same on the particular table you are querying about…I just showed the general format for using COUNT because COUNT does not error when the COUNT is ZERO and…I suppose that I should mention that you can assign it to a variable, too. (e.g. SELECT COUNT(*) AS myVarName…) –  jinzai yesterday
1  
I can't see how this would be better than Mark's query. SELECT 1 ... does not error either. –  ypercube yesterday
add comment
up vote -1 down vote

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' ;

share|improve this answer
3  
No you did not understood correctly. Also check The case against INFORMATION_SCHEMA views from @AaronBertrand –  Kin yesterday
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.