Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon in SQL.

Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

The Reason for this error is when you are calling CTE Comman Table Expression Inside a Batch you have to start the cte with Semicolon otherwise it will throw the above error

To Demonstrate this i will create a table

CREATE TABLE [dbo].[Employee1_Errors](

[Empid] [int] IDENTITY(1,1) NOT NULL,

[EmpName] [nvarchar](50) NULL,

[EmpSalary] [float] NULL,

EmpDob datetime

) ON [PRIMARY]

GO

insert into [Employee1_Errors] values ('vikas',2000,(getdate()-100))

insert into [Employee1_Errors] values ('vikas1',1000,(getdate()-200))

insert into [Employee1_Errors] values ('vikas2',3000,(getdate()-300))

insert into [Employee1_Errors] values ('vikas3',3000,(getdate()-400))

insert into [Employee1_Errors] values ('vikas4',3000,(getdate()-500))

select * from [Employee1_Errors]

Now we will display Result through cte where Salary greater than 2000 and Dateofbirth is less then current Date

declare @date datetime

set @date=GETDATE()

with cte as

( 

select * from [Employee1_Errors]

)

select * from cte where empdob <@date and [EmpSalary]>=2000

Msg 319, Level 15, State 1, Line 3

Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

The Reason for this Error is we are using CTE in between the batch so in order to remove error we have to terminate previous Statement by semicolon or start CTE with semicolon

declare @date datetime

set @date=GETDATE()

;with cte as

( 

select * from [Employee1_Errors]

)

select * from cte where empdob <@date and [EmpSalary]>=2000

This will Display the output without any Error

Note:It's always a good practise whenver you are using CTE always start it will Semi colon so that you can avoid these type of errors while working with CTE