Wednesday 3 June 2015

Error occurs when CTE Alias is ‘RESULT’ - Incorrect syntax near 'RESULT'

Few days back one of my colleague ran into a very strange issue when he was asked to do an alteration to an existing procedure. The procedure was having a SELECT statement which included few Common Table Expressions. It has been running without an issue, until we tried to save the alteration. The alteration was a pretty simple one which was just to add couple of more columns. The query was similar to the one shown below

;WITH RESULT AS(
    /* Your logic  */
    SELECT GETDATE() AS DTE
)
SELECT * FROM RESULT


How ever when we tried to save the changes, it was throwing the following error.


Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'RESULT'.

But when we go through the query we couldn’t find any issues, and it seems the syntax was quite accurate. So we tried a different server and was able to save the changes without any error. The only difference was one server was running SQL Server 2012 which is having a higher build

Works fine on the following build



Microsoft SQL Server 2012 - 11.0.5058.0 (X64)
    May 14 2014 18:34:29
    Copyright (c) Microsoft Corporation
    Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)


Throws an error on the following build



Microsoft SQL Server 2012 - 11.0.2100.60 (X64)
    Feb 10 2012 19:39:15
    Copyright (c) Microsoft Corporation
    Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)


And when googled we could find that ‘RESULT’ is a future reserved keyword:


https://msdn.microsoft.com/en-us/library/ms189822.aspx


image


But we were still left out with a question of why it failed on an earlier build but succeeded on a most recent build. Please feel free to comment on this if you have further information.