Use column if it exists, another if doesn't in SQL Server

Question

I have a number of SQL Server databases (different versions from 2012 to 2019). The schema in each one is very similar but not exactly the same. For example, there's table ORDERS, which has about 50 columns - and one column is called differently in two different databases:

in DB1: select p_user from orders
in DB2: select userpk from orders

Note that I showed two databases above, but there are actually more than 20 - some are DB1 type, the others are DB2 type

I can't do much about these differences - they are historic - and changing the schema to match is not an option.

I want to be able to run the same SQL statement against all of these databases at once. I'd like to write the query in such a way that it would use one column if it exists and another if it doesn't. For example:

select
    case
        when COL_LENGTH('orders', 'p_user') IS NOT NULL
    then
        orders.p_user
    else
        orders.userpk
    end
from orders

This unfortunately doesn't work, as SQL server seems to try to evaluate both results regardless of whether the condition is true or false. The same thing happens if I use IIF function.

If I simply run

select
    case
        when COL_LENGTH('orders', 'p_user') IS NOT NULL
    then
        'orders.p_user'
    else
        'orders.userpk'
    end

then I do get the correct string, which means my condition is correct.

How can I formulate the SQL statement to use one or the other column based on whether the first one exists?

Why not create a view to unify the schema differences? Having to rewrite every single query is not just massively burdensome, it has performance implications as well. It is possible to write a query that refers to a non-existent column, by abusing the way name resolution works, but this should not be done unless there's really no other way -- even dynamic query generation is preferable to that. — Jeroen Mostert
– Jeroen Mostert, Commented Feb 18, 2020 at 12:06
@JeroenMostert I don't own the databases nor have any write access to them. I can't create any columns, views or anything else. — Aleks G
– Aleks G, Commented Feb 18, 2020 at 12:07
Will they allow creating a new database? You could create views in that with three-part naming (SELECT x AS y FROM legacydb.dbo.OldTable). It would take up "no room" since it needn't actually contain any data of its own. — Jeroen Mostert
– Jeroen Mostert, Commented Feb 18, 2020 at 12:08
The best solution is honestly to fix things up client-side in that case -- detect the scheme and generate queries according to the actual column names, using a template with a placeholder name. In other words, create "views" of your own. — Jeroen Mostert
– Jeroen Mostert, Commented Feb 18, 2020 at 12:11
The non-dynamic trick for this to abuse name resolution logic: SELECT T.ID, V.DoesNotExist FROM T CROSS JOIN (SELECT NULL) _(DoesNotExist) CROSS APPLY (SELECT DoesNotExist FROM T) V will pick DoesNotExist from T if it exists, and NULL otherwise (you can plug in another column there). Doing this in every single one of your queries is a massive pain, decreases maintainability and has potential performance issues, so I mention it only for completeness. — Jeroen Mostert
– Jeroen Mostert, Commented Feb 18, 2020 at 12:17

Peter B · Accepted Answer · 2020-02-18 12:12:27Z

2

If you can't change anything then your best (and maybe only) option is to use dynamic SQL. A query will only compile if all parts can be resolved at compile time (before anything runs) - which is why e.g. this will not compile:

IF COL_LENGTH('orders', 'p_user') IS NOT NULL THEN
    select p_user from orders
ELSE
    select userpk as p_user from orders
END

But this will work:

DECLARE @SQL NVARCHAR(MAX)

IF COL_LENGTH('orders', 'p_user') IS NOT NULL THEN
    SET @SQL = 'select p_user from orders'
ELSE
    SET @SQL = 'select userpk as p_user from orders'
END

EXEC (@SQL)

answered Feb 18, 2020 at 12:12

Peter B

24.7k5 gold badges42 silver badges94 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gordon Linoff · Accepted Answer · 2020-02-18 12:05:29Z

0

Fix your tables by adding a computed column:

alter table db1..orders
    add statuspk as (p_status);

(Or choose the other name.)

Then, your queries will just work without adding unnecessary complication to queries.

answered Feb 18, 2020 at 12:05

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

2 Comments

Aleks G Over a year ago

I did say that changing the schema is not an option. I don't own these database and do not have permission to make any changes. I have read-only access to them all.

Mark Kram Over a year ago

Use Dynamic SQL?

lptr · Accepted Answer · 2020-02-18 15:02:52Z

0

create table orders1(colA int, colB int, colABC int);
insert into orders1 values(1, 2, 3);
go
create table orders2(colA int, colB int, colKLM int);
insert into orders2 values(5, 6, 7);
go
create table orders3(colA int, colB int, colXYZ int);
insert into orders3 values(10, 11, 12);
go


select colA, colB, vcolname as [ABC_KLM_XYZ] 
from
(
select *, 
(select o.* for xml path(''), elements, type).query('
/*[local-name() = ("colABC", "colKLM", "colXYZ")][1]
').value('.', 'int') as vcolname
from orders1 as o
) as src;

select colA, colB, vcolname as [ABC_KLM_XYZ] 
from
(
select *, 
(select o.* for xml path(''), elements, type).query('
/*[local-name() = ("colABC", "colKLM", "colXYZ")][1]
').value('.', 'int') as vcolname
from orders2 as o
) as src;

select colA, colB, vcolname as [ABC_KLM_XYZ] 
from
(
select *, 
(select o.* for xml path(''), elements, type).query('
/*[local-name() = ("colABC", "colKLM", "colXYZ")][1]
').value('.', 'int') as vcolname
from orders3 as o
) as src;
go

drop table orders1
drop table orders2
drop table orders3
go

answered Feb 18, 2020 at 15:02

lptr

6,8582 gold badges10 silver badges16 bronze badges

2 Comments

Aleks G Over a year ago

As I stated in the question and multiple comments, I do not have write access to the database and thus cannot create anything. I only have read-only access to run queries.

lptr Over a year ago

select count(distinct vcolname) from ( select *,  (select o.* for xml path(''), elements, type).query(' /*[local-name() = ("p_user", "userpk")][1] ').value('.', 'int') as vcolname from orders as o ) as src;

Aleks G · Accepted Answer · 2020-02-18 16:38:56Z

0

I ended up using dynamic sql like so:

declare @query nvarchar(1000)
set @query  =  concat(
'select count(distinct ', (case when COL_LENGTH('orders', 'p_user') IS NOT NULL then 'orders.p_user' else 'orders.userpk' end), ')
from orders'
);
execute sp_executesql @query

This solved my immediate issue.

answered Feb 18, 2020 at 16:38

Aleks G

57.5k34 gold badges180 silver badges280 bronze badges

Collectives™ on Stack Overflow

Use column if it exists, another if doesn't in SQL Server

4 Answers 4

Comments

2 Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

2 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related