Including a sometimes missing column in TSQL if it's there (or alternative if it's not)

Question

I have a weird table that I have to deal with that sometimes has a disappearing column. If the column is there, I need to use it. But if not, I need to account for that and use an alternative. But when I try this code when the column is missing, SSMS throws an error (Invalid column name 'DOB'). Shouldn't this short circuit if the column isn't there and never get to the part where it calls the column? So why the error message? Any solutions? Thanks in advance for any help!

SELECT
    SalesClients.ClientName,
    (CASE 
        WHEN (COL_LENGTH('dbo.SalesClients', 'DOB') IS NULL) 
           THEN DATEADD(month, -SalesClients.AgeInMonth, GETDATE())
        WHEN SalesClients.DOB IS NULL 
           THEN DATEADD(month, -SalesClients.AgeInMonth, GETDATE())
        ELSE SalesClients.DOB
     END) AS DOB
FROM 
    dbo.SalesClients AS SalesClients

Solve the problem. columns randomly disappearing from tables is bad in many other ways.. — D Stanley, Aug 08 '18 at 17:11
"Solve the problem. columns randomly disappearing from tables is bad in many other ways." Unfortunately, that's not an option. If it were, I would have (of course) done it. — Jim Sloun, Aug 08 '18 at 17:13
How are you using this SQL? I think it is bad idea to try to solve the issue. Why does column keep disappearing? If this table is created by a program or ETL, could you tweak it to store null values when no data is available? — rs., Aug 08 '18 at 17:21
First, SQL Server does not do the kind of short-circuiting that you are talking about, and you cannot control it within a single query either. Secondly, static queries must have static columnsets, that's not an option. So the only out here is to use dynamic SQL queries. — RBarryYoung, Aug 08 '18 at 17:21
I'd be inclindd to use an OR statement and combine the two WHEN conditions, since you appear to be doing the same thing in each of those cases... Have you looked at COALESCE too? — JGFMK, Aug 08 '18 at 17:29
If you get the rows back as XML and then query the XML for the (possible) column ... . — HABO, Aug 08 '18 at 17:50
@RBarryYoung, *the only out here is to use dynamic SQL queries* ... There is a hack with XML, see my answer :-D — Shnugo, Aug 08 '18 at 21:24
@HABO, XML is a really fancy hack to solve some things generically, where you would need dynamic SQL otherwise. In this case it seems to be rather easy, see my answer... — Shnugo, Aug 08 '18 at 21:31
@Shnugo I knew it was possible based on one of your [answers](https://stackoverflow.com/questions/51633476/how-to-get-the-highest-value-in-a-row-together-with-the-column-name) to another question, and I was flailing on an example. Sadly, my xml-fu is weak. — HABO, Aug 08 '18 at 21:42

Shnugo · Answer 1 · 2018-08-08T21:32:51.870

If performance matters (especially with many rows and large columns) you probably have to use dynamic SQL, but there is another approach using XML's generic abilities.

This won't be fast, but it can be fully inlined (in a VIEW or iTVF).

SELECT * FOR XML RAW

will generate an XML, where every row is one element <row>, while the columns are attributes. This allows a generic approach like here:

DECLARE @tbl TABLE(ID INT IDENTITY,SomeString VARCHAR(100));
INSERT INTO @tbl VALUES('test1'),('test2');

DECLARE @tbl2 TABLE(ID INT IDENTITY,SomeString VARCHAR(100),DOB DATE);
INSERT INTO @tbl2 VALUES('test1','20180101'),('test2','20180202');

--try this with @tbl and with @tbl2, it works in both cases
SELECT r.value('@ID','int') AS ID
      ,r.value('@DOB','date') AS DOB
FROM
(SELECT * FROM @tbl2 FOR XML RAW, TYPE) A(x)
CROSS APPLY x.nodes('/row') B(r);

The FOR XML RAW will generate something like this

<row ID="1" SomeString="test1" DOB="2018-01-01" />
<row ID="2" SomeString="test2" DOB="2018-02-02" />

... and .nodes('/row') will return each <row> as a table's row (a derived table).

The .value() method will return just a NULL if an attribute is not found.

Heh, very nice, I've always thought that you could do some tricks like that by aggregating XML structures and strings and then shredding them in different ways, but I could never quite work out the details. — RBarryYoung, Aug 08 '18 at 21:36

score 0 · Answer 2 · answered Aug 08 '18 at 17:50

Try this

SELECT 
    SalesClients.ClientName,

CASE WHEN EXISTS(
                SELECT 
                    COLUMN_NAME 
                FROM 
                    INFORMATION_SCHEMA.COLUMNS 
                WHERE 
                    TABLE_NAME = 'SalesClients' 
                    AND COLUMN_NAME = 'DOB'
                )
    THEN 
        SalesClients.DOB
    ELSE
        DATEADD(month, -SalesClients.AgeInMonth, GETDATE()) 
    END AS DOB
FROM 
    dbo.SalesClients AS SalesClients

it's a good try, but this does not work in a stored proc, it will fail when the column is not there. try it. solution would need to use dynamic sql. — Kevin Suchlicki, Aug 08 '18 at 18:45

Including a sometimes missing column in TSQL if it's there (or alternative if it's not)

2 Answers2