I'm creating a dynamic SQL view with UNION ALL, and have successfully created what I want, when I print the output I can copy it to a new query window, run the query that was built by the dynamic SQL and create the view.
However, if I try and run a procedure to create the view i.e. EXECUTE dbo.sp_executeSQL @mysql
it fails at the union section and I can't figure out why it is not liking union.
My output code is like:
CREATE VIEW MyView AS
select Table.columnA AS Code
, Table.columnB AS CodeDescription
Where Table.x = 'Value' UNION ALL
select Table2.columnA AS Code
, Table2.columnB AS CodeDescription
Where Table2.x = 'Value'
............continues for another 8 tables
I've got the union all statement at the start of the while loop and then it removes it before the end of the loop that builds each dynamic table so it doesn't add a UNION ALL Statement after the last statement.
@UnionSQL = ' UNION ALL '
As I said when I pull the generated statement out and run it, it runs fine
Any thoughts why the procedure isn't liking the union statement from the dynamic code?
I'm not quite sure of the exact SQL version but I know it's past the 2012 version as it was all updated not long ago.