从视图中的动态表名中选择

问题描述

我的客户每年都会创建一个新表，其中的年份包含在我必须在我的 SQL Server 数据库的新视图中使用的名称中.我已经在一个查询中解决了这个问题:

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?

提前致谢.

推荐答案

我不知道它是否适合您，但您可以动态创建视图本身.像这样:

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)