Select from a dynamic table name in a view

Question

My customer creates yearly a new table with the year included in the name that I have to use in a new view of my SQL Server database. I have solved this problem in a single query:

DECLARE @SQLString nvarchar(500)
SET @SQLString = 'SELECT * FROM [MYDATABASE].[dbo].[MYTABLE_'+cast(YEAR(GETDATE()) as varchar(4))+']'
EXECUTE sp_executesql @SQLString

but I cannot use an execute statement in a view. I've also tryied to move it to a function but the problem is the same. ¿What could I do?

Thanks in advance.

Acicula

You can't in a VIEW. You'll have to use a Stored Procedure for this type of logic. – Philter

You cannot have a view that uses dynamic SQL. Nor a table valued function. Fix your data model and put all the data for all the years in a single table. Voila! You'll find that your code is much simpler. – Retina

Why are you storing the data in different tables in the first place? – Coel

Thanks for your responses. Gordon, Salman, that tables are not from my develop. They belong from a third party develop used for years by my customer so I cannot change that data model :( – Acicula

Well, I think you'll find your customer is easier to persuade than SQL Server development team. Either change the data model or use a stored procedure. A view or UDF will not help you with this. – Motorcade

First figure out how long you'll have this job. Then create a view using union with enough yearly tables and where clauses to handle the data until you're gone. <Tongue firmly in cheek.> – Avesta

So a table is created once every year at an expected point in time. Expand that activity to also alter a view. That view will simply union all of the existing tables. Your code will use that view and simply select from it for whichever period of time is needed by including the appropriate where clause. But this seems to be a XY problem - why must a view be involved? – Legged

Answer 1

I don't know if it is an option for you, but you can dynamically create a view itself. Something like this:

declare @sql varchar(1000)
,@sql2 varchar(1000)

set @sql = ('create view x as select * from MyTable_' + convert(varchar(10),year(getdate())) + ' go')

set @sql2 = 'select * from x'

exec (@sql)
exec (@sql2)

Sine

Hi ser_nicky. Unfortunately this is not an option for me. Thanks for your help. – Acicula

Answer 2

Since your view only references the "latest" table you can refer to that table using a synonym:

create synonym dbo.CurrentYearVendorTable for dbo.VendorStuffFor2019;

create view dbo.MyView as
  select *
    from dbo.CurrentYearVendorTable; -- Note: Reference to synonym, not table.

When the new table is created replace the synonym and update the view:

drop synonym dbo.CurrentYearVendorTable;
create synonym dbo.CurrentYearVendorTable for dbo.VendorStuffFor2020;
execute sp_refreshview @viewname = 'dbo.MyView';

For other uses it may simply suffice the use the synonym in queries.

Avesta