I am pretty new to SQL and might be doing something wrong here so I hope someone can help me with this.
I have a longer stored procedure that ends with the below. The idea here is to combine two queries into one so that I don't need a separate stored procedure but still have the results separated as one selects by a country (selCountry) and the other selects by a date (selDate).
When I try to save it this way I get the error: "Incorrect syntax near the keyword 'UNION'. When I run them in separate procedures then everything works fine.
-- create final output
SELECT CONVERT(DATETIME, dDay) AS dDay,
CONVERT(VARCHAR(11), dDay, 106) AS dLong,
CONVERT(VARCHAR(10), dDay, 126) AS dShort,
countries,
regions,
mode,
dName,
dDesc
FROM @tempFix
WHERE countries LIKE '%'+@selCountry+'%'
OR regions LIKE '%'+@selCountry+'%'
ORDER BY dDay
UNION ALL
SELECT CONVERT(DATETIME, dDay) AS dDay,
CONVERT(VARCHAR(11), dDay, 106) AS dLong,
CONVERT(VARCHAR(10), dDay, 126) AS dShort,
countries,
regions,
mode,
dName,
dDesc
FROM @tempFix
WHERE DAY(dDay) = DAY(@selDate)
AND MONTH(dDay) = MONTH(@selDate)
FOR XML PATH('dDays'), ELEMENTS, TYPE, ROOT('root')
Many thanks in advance for any help with this, Mike.
Please remove the ORDER BY and add it to the very end. You cannot place ORDER BY before UNION / UNION ALL.
Here's more information about this syntax.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments