SQL Server, .NET, Python and Other Programming Troubleshooting Articles

Resolve SQL Server CTE Error – Incorrect syntax near ‘)’.

by

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!

Essential SQL Server Development Tips for SQL Developers - Online Course
(Lifetime Access/ Live Demos / Downloadable Resources and more!)

Learn More

 

 

Featured Online Courses:

 

Read Also:

 

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: 1 Star2 Stars3 Stars4 Stars5 Stars (2 votes, average: 5.00 out of 5)

Loading...

Reference: SQLNetHub.com (https://www.sqlnethub.com)

© SQLNetHub

Views: 6,730