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

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
BasssqueAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
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

BasssqueAuthor 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 DeveloperCommented:
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.
BasssqueAuthor Commented:
I new table is added once a year, none are ever removed.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.