Create Dynamic View with UNION failing

Transact-SQL
1

Hi

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
, Table.CodeID
From Table
Where Table.x = 'Value' UNION ALL

select Table2.columnA AS Code
, Table2.columnB AS CodeDescription
, Table2.CodeID
From Table2
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?

Thanks

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.

G

2

Would help if you included the error you are getting and the code used to generate the view.

3

I'm running this procedure:

sp_executeSQL @SQL

Msg 102, Level 15, State 1, Procedure view_my_view, Line 22 [Batch Start Line 0]
Incorrect syntax near 'ALL'.

4

Please show us the code where the error happens namely the procedure

5

I have printed the output of the sp_executeSQL and the error is at the line where "UNION ALL" is in the statement, The next statement after union all is obviously the same query but the next day's values that I am unioning. Does the procedure need the UNION ALL to be on a separate line?:

CREATE VIEW view_my_view AS 
select TableAlias.valueID AS Code
, TableAlias.Description AS CodeDescription
, TableAlias.userID
from Table TableAlias 
									INNER JOIN
									(select MAX(valueID) MaxValueID, userID
									from Table 
									group by userID
									) a
  on TableAlias.valueID = a.MaxValueID

  INNER JOIN table2 abc on TableAlias.[Acode1] = abc.Code
  INNER JOIN table3 umatr ON TableAlias.[NRCode1] = umatr.Code
  INNER JOIN table4 uml ON TableAlias.Suncode1 = uml.Code
  INNER JOIN table5 umd ON TableAlias.SunFacCode1 = umd.Code
  INNER JOIN Table6_Complete2 TTC6 
  ON abc.CodeDescription = TTC6.ActDesc1 AND TableAlias.userID = TTC6.userID AND TableAlias.SunFacCode1 = TTC6.DiscDesc AND TableAlias.Suncode1 = TTC6.Loc1
  where TableAlias.[NRCode1] is not null
  and TableAlias.[YN1] = 2
  and TTC6.DayName = 'Sunday'
  and TableAlias.ADate >= TTC6.Date  UNION ALL
Msg 102, Level 15, State 1, Procedure view_my_view, Line 22 [Batch Start Line 0]
Incorrect syntax near 'ALL'.
6

Look at the last trailing UNION ALL

7

Need to see the code you are using to generate the view. What you have shown here has no statement after the UNION ALL - which is going to cause an error.

Also - run SELECT @@VERSION and provide that information. If you are on a version greater than 2017 there may be other options available.

The problem is obviously how you are generating the code to create the view - therefore we need to see that code in order to be able to help you.

8

Version is 2019

I'm not sure if this is what you want to see, it is the dynamic code that generateds the view:

@SubQuerySelect VARCHAR(100) = 'CREATE VIEW view_my_view AS '
-- This is set to clear in the 2nd iteration of the loop so that it only populates the
-- variable at the first statement to create the view

@ConcatActivity
-- This is to give a description that increments by +1 each loop iteration
-- i.e. 'Value Description 1', 'Value Description 2' etc.

@TableName2, @TableName, @ActNo2, @LocActNo, @LocDay, @DisFac
--These are all set in the loop as well with an iterating number, @tablename is the table alias

@UnionSQL
-- This is populated with statement 'UNION ALL' and is set at the first
--iteration of loop so it starts adding the statement after the first
--query and is cleared on the last iteration of the loop to avoid putting
-- the statement after the last query.

SELECT @sql2 =	N' '+ @SubQuerySelect + '
select '+ @TableName +'.valueID  AS Code
, Concat('''+ @ConcatActivity + ''' ,uma.CodeDescription) AS CodeDescription
, '+ @TableName +'.userID
from '+ @TableName2 + ' ' + @TableName +' 
									INNER JOIN
									(select MAX(valueID) MaxValueID, userID
									from '+ @TableName2 +' 
									group by userID
									) a
  on '+ @TableName +'.valueID  = a.MaxValueID

  INNER JOIN table2 uma on '+ @TableName +'.[Acode1'+ Cast(@ActNo2 AS NVARCHAR(5)) +'] = uma.Code
  INNER JOIN table3 umatr ON '+ @TableName +'.[NRCode1'+ Cast(@ActNo2 AS NVARCHAR(5)) +'] = umatr.Code
  INNER JOIN table4 uml ON '+ @TableName +'.'+ @LocDay + Cast(@LocActNo AS NVARCHAR(5))+' = uml.Code
  INNER JOIN table5  umd ON '+ @TableName +'.'+ @DisFac + Cast(@ActNo2 AS NVARCHAR(5)) +' = umd.Code
  INNER JOIN Table6_Complete2 TTC 
  ON uma.CodeDescription = TTC.Activity1 AND '+ @TableName +'.userID = ttc.userID AND '+ @TableName +'.'+ @DisFac + Cast(@ActNo2 AS NVARCHAR(5)) +' = TTC.DiscDesc AND '+ @TableName +'.'+ @LocDay + Cast(@LocActNo AS NVARCHAR(5))+' = TTC.Loc1
  where '+ @TableName +'.[NRCode1'+ Cast(@ActNo2 AS NVARCHAR(5)) +'] is not null
  and '+ @TableName +'.[YN'+ Cast(@ActNoChar AS NVARCHAR(5)) +'] = 2
  and TTC.Day_Name = '''+Cast(@DayName2 AS NVARCHAR(50)) +'''
  and '+ @TableName +'.ADate  >= TTC.ADate '+'' + CHAR(13) + CHAR(10) + ''+ @UnionSQL +''

I can print the output, copy the statement, run it in a window and it will create the view, it's only when I run the @sql2 into sp_executeSQL procedure that it fails on the UNION ALL section right on the first query.

Thanks

G

9

Looks like maybe the loop stops at 1 so you have UNION ALL but nothing to insert there

10

When I run the code and print it out it goes through all the loop iterations & generates the statement with all queries and the unions for each one.

Only when I run with sp_executeSQL does it fail.

11

That is only partial code - where is the code that performs the loop and where is the code that executes the generated code?

There is definitely something wrong with the code you have posted - which tells me that isn't the actual code you are using and won't show the issues you are trying to resolve. This could be as simple as an incorrectly defined variable - or it could be an issue with how you have built the loop but without being able to actually see that code there is no way we can help.

12

Does @sql2 have the data type nvarchar(MAX)?

13

Yes, as below:

DECLARE @sql2 NVARCHAR(max)

Just wondering if maybe I was running the sp_executeSQL in the wrong place. If my loops are nested it would be right at the end just before the last end statement?

Begin 

While
   Do things.....
   While 
     Do things.....
   End

EXECUTE sp_executeSQL @Sql2
End
14

It looks like you are adding the @UnionSQL to the concatenation of @sql2. So @SQL2 looks like select fields from .. union all

15

Should I include the union all somewhere else other than in the dynamic script?

16

no, it just when you are creating the dynamic string, you'll need to trim off the last union all. The string would look like Select 1 union all select 2 union all select 3 union all. You'll need to set the variable to Left(variable, len(Variable) - 10) to remove the last union all

17

I'd set the union variable at the last iteration of the loop to:

@UnionSQL = ''

That stops it adding the statement to the end of the last query.

18

all we have to go on is the code you supplied. If you can put a print (@SQL2) statement or print whatever it is you are trying to execute, we can be more helpful

19

Try printing the statements before you execute them:
PRINT @sql1
PRINT @sql2
If they work then there is something critically wrong with SQL Server which I somehow doubt