sql - View using dynamic tables

Question

How can i convert the following query into something that i can create a view with.

create view hourlyBilling_view as
declare @sql varchar(max)
set @sql = ''
select @sql = @sql + case len(@sql) when 0 then '' else ' UNION ALL ' end + '
  SELECT * FROM [' + table_name + '] where line_item_6 is not null ' 
from
  information_schema.tables where table_name like 'Billing_trap_2019%'
exec (@sql) 

It give me an error about the declare can not be used.

I have checked online and see it might be possible, but I don't have the SQL skills to figure it out.

标签： sqlsql-serversql-view

Answer 1

不能在视图中使用动态 sql，甚至不能在表值函数中使用。最接近您想要的是存储过程。