Hi friends, in this post, we will be discussing about how you can resolve the SQL Server CTE Error: Incorrect syntax near ‘)’.
What are Common Table Expressions (CTEs)?
In SQL Server and more specifically in T-SQL, a CTE is a temporary named result set, from which you can then select records. CTEs are temporary objects and are not stored on disk.
CTEs vs Views
Even though CTEs might sound similar to database views, there is one major difference: views are database objects that are stored physically on disk but CTEs, as mentioned above, are just temporary objects and thus they are not stored on disk.
Let’s Reproduce the Incorrect syntax near ‘)’ error
Now, about the Incorrect syntax near ‘)’ error, let’s see a relevant example, via which we can reproduce the error.
With the below T-SQL script, I’m trying to define a CTE in SQL Server:
WITH cte_Customers AS ( SELECT * FROM dbo.tblCustomers WHERE id>2 )
However, when I try to execute the above script, I will get the error message:
Msg 102, Level 15, State 1, Line 6
Incorrect syntax near ‘)’.
How to Resolve the Issue
Resolving the above issue, is very easy.
The key thing to remember when defining a CTE in SQL Server, is that in its definition, you must always include a SELECT, DELETE, INSERT or UPDATE statement, that references one or more columns returned by the CTE.
So, by continuing our example, in order to make the definition of the CTE valid, I can include for instance, a SELECT statement.
Let’s see the new example:
WITH cte_Customers AS ( SELECT * FROM dbo.tblCustomers WHERE id>2 ) SELECT * FROM cte_Customers
Now, if I execute the above script, I can see that my CTE works without errors.
Learn more about SQL Server Development – Enroll to our Course!
Enroll to our online course titled “Essential SQL Server Development Tips for SQL Developers” (special limited-time discount included in link) and sharpen your SQL Server database programming skills via a large set of tips on T-SQL and database development techniques. The course, among other, features over than 30 live demonstrations!
Featured Online Courses:
- SQL Server 2022: What’s New – New and Enhanced Features
- Working with Python on Windows and SQL Server Databases
- Introduction to Azure Database for MySQL
- Boost SQL Server Database Performance with In-Memory OLTP
- Introduction to Azure SQL Database for Beginners
- Essential SQL Server Administration Tips
- SQL Server Fundamentals – SQL Database for Beginners
- Essential SQL Server Development Tips for SQL Developers
- Introduction to Computer Programming for Beginners
- .NET Programming for Beginners – Windows Forms with C#
- SQL Server 2019: What’s New – New and Enhanced Features
- Entity Framework: Getting Started – Complete Beginners Guide
- Data Management for Beginners – Main Principles
- A Guide on How to Start and Monetize a Successful Blog
- Essential SQL Server Development Tips for SQL Developers (Course Preview)
- System.IO.FileLoadException: could not load file or assembly…
- The TempDB System Database in SQL Server
- SQL Server Installation and Setup Best Practices
- The feature you are trying to use is on a network resource that is unavailable
- SQL Server 2016: TempDB Enhancements
- tempdb growth
- Introduction to SQL Server Machine Learning Services
- Essential SQL Server Administration Tips
- What are SQL Server Statistics and Where are they Stored?
- Tip of the Week No.1 – SQL Server Always Encrypted
- Tip of the Week No.3 – TempDB Settings During Installation
- Tip of the Week No.6 – About SQL Server Temporary Tables
- Tip of the Week No.19 – What is the Database First Workflow in Entity Framework?
- Tip of the Week No.20 – SQL Server Surface Area
- Within Which Context Does SQL Server Access Network Resources?
- Troubleshooting the File Activation Error in SQL Server
- SQL Server Error 262: CREATE DATABASE permission denied in database – How to Resolve
- Resolving “System.IO.IOException: The process cannot access the file because it is being used by another process” CLR Error
- Check all our Weekly Tips!
Subscribe to our newsletter and stay up to date!
Subscribe to our YouTube channel (SQLNetHub TV)
Easily generate snippets with Snippets Generator!
Secure your databases using DBA Security Advisor!
Generate dynamic T-SQL scripts with Dynamic SQL Generator!
Check our latest software releases!
Check our eBooks!
Rate this article:
Reference: SQLNetHub.com (https://www.sqlnethub.com)
Artemakis Artemiou is a Senior Database and Software Architect, Certified Database, Cloud and AI professional, Author, a 9 Times Microsoft Data Platform MVP (2009-2018). He has over 20 years of experience in the IT industry in various roles. Artemakis is the founder of SQLNetHub. Artemakis is the creator of the well-known software tools Snippets Generator and DBA Security Advisor. Also, he is the author of many eBooks on SQL Server. Moreover, Artemakis teaches on Udemy, you can check his courses here.