Few days ago, one of my colleagues 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(
SELECT GETDATE() AS DTE
)
SELECT * FROM RESULT
However, 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
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.