问题描述
我的客户每年都会创建一个新表,其中的年份包含在我必须在我的 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)