65.9K
CodeProject is changing. Read more.
Home

Error Occurs When CTE Alias is ‘RESULT’ - Incorrect Syntax Near 'RESULT'

starIconstarIconstarIconstarIconemptyStarIcon

4.00/5 (1 vote)

Jun 3, 2015

CPOL

1 min read

viewsIcon

10170

Error occurs when CTE Alias is ‘RESULT’ - Incorrect syntax near 'RESULT'. Succeeds on some SQL Server databases but fails on others.

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( 
    /* Your logic */ 
    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.