Declaring and using variables with a select statement if a table exists

Basssque
Basssque used Ask the Experts™
on
This is a read only sql query being written in a SQL editor
The ultimate goal here is to print all 5 columns if the table does exist OR to print the year and 4 NULL columns if the table does not exist.

The tables I need to check if exists are sba_2017_math, sba_2018_math and sba_2019_math
I wrote this basic select statement to return t or f if the table exists or not.
Can I put that t/f into a variable?  I need to do that for all 3 years separately.
SELECT EXISTS (
				SELECT 1
				FROM   information_schema.tables 
				WHERE  table_schema = 'state_data_ct'
				AND    table_name = 'sba_2019_math'
			  )

Open in new window


Then can I use that variable to somehow run the following query?

select 
'2017' as year,
case when @exists2017 = 't' then column1 else null end as column1,
case when @exists2017 = 't' then column2 else null end as column2,
case when @exists2017 = 't' then column3 else null end as column3,
case when @exists2017 = 't' then column4 else null end as column4
from state_data_ct.sba_2017_math
UNION ALL
select 
'2018' as year,
case when @exists2018= 't' then column1 else null end as column1,
case when @exists2018 = 't' then column2 else null end as column2,
case when @exists2018 = 't' then column3 else null end as column3,
case when @exists2018 = 't' then column4 else null end as column4
from state_data_ct.sba_2018_math
UNION ALL
select 
'2019' as year,
case when @exists2019= 't' then column1 else null end as column1,
case when @exists2019 = 't' then column2 else null end as column2,
case when @exists2019 = 't' then column3 else null end as column3,
case when @exists2019 = 't' then column4 else null end as column4
from state_data_ct.sba_2019_math

Open in new window


I've never worked with variables before in a sql query so I'm not sure exactly how to declare the variables or use them properly.  
If you have a suggestion of a better way to do this, I'm open to them.

Thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
ste5anSenior Developer

Commented:
First of all: This looks like a design flaw. Normally you would have one table and a year discriminator column.

The other approach is to use a view. Cause tables are static, you should create/alter the view when a new table is added or one is removed. In this case, you would just create it with a UNION like above.

Despite that, you can use dynamic SQL. Either to create that view or to directly create the wanted output. But this depends on your SQL dialect. In T-SQL (SQL Server). Something like:

DECLARE @Statement NVARCHAR(MAX) = STUFF((
    SELECT  'UNION ALL SELECT ' + SUBSTRING(T.name, 5, 4) +
            ' AS year, column1, column2, column3, column4 FROM state_data_ct.' + T.name
    FROM    sys.tables T
    WHERE   SCHEMA_NAME(T.schema_id) = 'state_data_ct'
        AND T.name LIKE 'sba_%_math'
    FOR XML PATH('')), 1, 10, ''));

SET @Statement = 'SELECT ISNULL(T.year, V.number) AS year, T.column1, T.column2, T.column3, T.column4 ' +
        'FROM master..spt_values V LEFT JOIN ' +
        '( + ' @Statement + ') AS Q ON Q.year = V.number ' +
        'WHERE V.type = ''P'' AND V.number BETWEEN 2010 AND 2020;'

EXECUTE (@Statement);

Open in new window

Author

Commented:
ste5an,

I agree that having a separate table for each year is not ideal but the database is part of a 3rd party product and I can't change the design.
I think the query you provided may need to be slightly modified though.  I put this post in the SQL forum because I'm using a SQL editor but the database is actually PostgreSQL.  When I run the query, it doesn't like the @ characters.
ste5anSenior Developer

Commented:
The question is: how often do you get a new table or is an old table removed?

Cause I would create a UNION over all existing tables first. This would solve the normalization issue. Then you can just join in a number tally for the non-existing years.

Author

Commented:
I new table is added once a year, none are ever removed.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial