Click here to Skip to main content
15,867,756 members
Articles / Database Development / SQL Server / SQL Server 2012

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

Rate me:
Please Sign up or sign in to vote.
4.00/5 (1 vote)
3 Jun 2015CPOL1 min read 9.7K   1
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:

SQL
;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

Image 1

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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Technical Lead Air Liquide Industrial Services (Singapore)
Singapore Singapore
My passion lies in building business intelligence and data-based solutions, writing about things I work with and talking about it. New technologies relevant to my line of work interest me and I am often seen playing with early releases of such technologies.

My current role involves architecting and building a variety of data solutions, providing database maintenance and administration support, building the organization’s data practice, and training and mentoring peers.

My aspiration over the next several years is to achieve higher competency and recognition in the field of Data Analytics and move into a career of data science.


Specialities: SQL Server, T-SQL Development, SQL Server Administration, SSRS, SSIS, C#, ASP.Net, Crystal Reports

Comments and Discussions

 
QuestionLooks like a bug fixed in SP1 Pin
MartinSmith100000022-Nov-15 6:36
MartinSmith100000022-Nov-15 6:36 
Probably caused by the new
SQL
WITH RESULT SETS
syntax.

http://stackoverflow.com/a/33846740/73226[^]

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.