0

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.

catalyph
  • 101
  • 9
  • 1
    You simply can not use dynamic SQL in a view. – John Cappelletti Jan 31 '19 at 19:28
  • Possible duplicate of [Create A View With Dynamic Sql](https://stackoverflow.com/questions/21173381/create-a-view-with-dynamic-sql) – Martin Jan 31 '19 at 19:30
  • The usual approach, if a stored procedure is unsuitable, is to construct the view text dynamically (e.g. produce a `CREATE / ALTER VIEW` statement and execute that). That needs to be redone if the tables involved change; you could do that as part of a job. All this is assuming these `billing_trap_2019%` tables should exist in the first place, instead of you just putting data in *one* `billing_trap_2019` table (with an extra column to differentiate sources, if necessary). – Jeroen Mostert Jan 31 '19 at 19:30
  • This has all the tell tale signs of an [xy problem](http://xyproblem.info/) – Sean Lange Jan 31 '19 at 19:32

1 Answers1

1

It is not possible to use dynamic sql in a view, or even a table-valued function. The closest you can get to what you want is a stored procedure.

Tab Alleman
  • 31,483
  • 7
  • 36
  • 52