Last post Jan 15, 2016 04:33 PM by ghirst
Jan 15, 2016 04:17 PM|ghirst|LINK
I'm trying to make a dynamic SQL with a UNION ALL but am getting the error:
Msg 102, Level 15, State 1, Line 7
Incorrect syntax near 'ALL'.
If I put Union All into the second select it works but that's not going to work in the real work application of this. It might be my IF statement. I only want to add 'Union All' if there is something in QueryString and then add the second part of the query.
DECLARE @QUERY NVARCHAR(MAX) = ''
DECLARE @QUERYSTRING NVARCHAR(MAX) = ''
DECLARE @sTypeOfUtility NVARCHAR(MAX) = '1234xyz'
If @sTypeOfUtility = '2' or @sTypeOfUtility = '1234xyz'
SET @QUERYSTRING =
'SELECT a.ID, p.UPRN, a.OverallRiskCategory, a.TypeOfUtility, a.SurveyDate, a.NextAsbestosSurveyDue, a.Recommendations,
TblAsbestos AS A
LEFT JOIN TBLPROPERTY AS P
ON a.UPRN = p.UPRN
WHERE 1 =1 AND OverallRiskCategory = ''B'''
If @sTypeOfUtility = '3' or @sTypeOfUtility = '1234xyz'
IF @QUERYSTRING <> ''
SET @QUERYSTRING = @QUERYSTRING + ' UNION ALL '
SET @QUERYSTRING = @QUERYSTRING +
' SELECT ID, FIREUPRN, RiskRating , TypeOfUtility, FRADate, NextSurveyDue, Task, StatusOfIssue, '''' As ItemRef from TblFire'
SET @QUERY = @QUERYSTRING
Jan 15, 2016 04:30 PM|mgebhard|LINK
Comment the EXECUTE function and print the contents of @query.
I think you'll see the error...
Jan 15, 2016 04:33 PM|ghirst|LINK
Opps, Friday moment!