CODEWITHSUNDEEP
sqlsql-server
Aleks G
Aleks G

Reputation: 57306

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

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:

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?

Upvotes: 0

Views: 1988

Answers (4)

Aleks G
Aleks G

Reputation: 57306

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.

Upvotes: 0

lptr
lptr

Reputation: 6788

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

Upvotes: 0

Peter B
Peter B

Reputation: 24136

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)

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269463

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.

Upvotes: 0

Related Questions