从视图中的动态表名中选择
我的客户每年都会创建一个新表，其中的年份包含在我必须在我的 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)
相关文章