Hello I’m trying to figure out why switching my compatability mode from 80 to 100 in MSSQL broke my function below?
Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (X64) Apr 22 2011 19:23:43
Copyright (c) Microsoft Corporation Express Edition with Advanced Services (64-bit) on
Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
Here is my function:
GO
ALTER FUNCTION [dbo].[GetRoot]
(
@Param1 int
)
RETURNS varchar(50)
AS
BEGIN
DECLARE @ReturnValue varchar(50)
with results as
(
select parentouid,net_ouid from net_ou where net_ouid=@Param1
union all
select t2.parentouid,t2.net_ouid from net_ou t2
inner join results t1 on t1.parentouid = t2.net_ouid where t2.parentouid <> t1.net_ouid
)
select @ReturnValue = net_ou.displayname
from NET_OU RIGHT OUTER JOIN
results ON net_ou.net_ouid = results.ParentouID where results.parentouid=results.net_ouid
RETURN @ReturnValue
END
asked Aug 11, 2011 at 20:30
Try throwing a semi colon in front of the with:
;with results as
(
select parentouid,net_ouid from net_ou where net_ouid=@Param1
union all
select t2.parentouid,t2.net_ouid from net_ou t2
inner join results t1 on t1.parentouid = t2.net_ouid where t2.parentouid <> t1.net_ouid
)
Give this article a read to understand why you need to do that. Snipit:
However, if the CTE is not the first statement in the batch, you must
precede the WITH keyword with a semicolon. As a best practice, I
prefer to prefix all of my CTEs with a semicolon—I find this
consistent approach easier than having to remember whether I need a
semicolon or not.
Personally, I don’t do it for every CTE, but if that makes things easier for you it won’t hurt anything.
answered Aug 11, 2011 at 20:31
Abe MiesslerAbe Miessler
82.1k99 gold badges304 silver badges484 bronze badges
4
Add a semicolon before WITH:
;with results as
(
select parentouid,net_ouid from net_ou where net_ouid=@Param1
union all
select t2.parentouid,t2.net_ouid from net_ou t2
inner join results t1 on t1.parentouid = t2.net_ouid where t2.parentouid <> t1.net_ouid
)
select @ReturnValue = net_ou.displayname
from NET_OU RIGHT OUTER JOIN
results ON net_ou.net_ouid = results.ParentouID where results.parentouid=results.net_ouid
RETURN @ReturnValue
END
CTE declarations need to be the first command in the batch.
answered Aug 11, 2011 at 20:32
JNKJNK
63k15 gold badges121 silver badges138 bronze badges
I would suggest that you adopt the practice of ending all statements with a semicolon. This is part of the ANSI standard and will help you when need to work on another database. SQL Server are moving towards this in any case. Many more commands require semicolons now in SQL Server 2012.
E.g.
ALTER FUNCTION [dbo].[GetRoot]
(@Param1 int)
RETURNS varchar(50)
AS
BEGIN
DECLARE @ReturnValue VARCHAR(50)
;
WITH cteResults
AS (SELECT parentouid
,net_ouid
FROM net_ou
WHERE net_ouid=@Param1
UNION ALL
SELECT t2.parentouid,t2.net_ouid
FROM net_ou t2
INNER JOIN results t1
ON t1.parentouid = t2.net_ouid
WHERE t2.parentouid <> t1.net_ouid )
SELECT @ReturnValue = net_ou.displayname
FROM net_ou
RIGHT JOIN cteResults
ON net_ou.net_ouid = results.ParentouID
WHERE results.parentouid=results.net_ouid
;
RETURN @ReturnValue
;
END
;
GO
As an added bonus it makes you queries a crap load easier to read. 
answered Aug 29, 2012 at 15:02
Joe HarrisJoe Harris
13.6k4 gold badges46 silver badges53 bronze badges
- Remove From My Forums
-
Question
-
i am writing a following with clause in sql server but i am getting an error-Incorrect syntax near the keyword ‘With’.
with
temp_table(userid)
as
(Select
distinct uid
from tableOfRecords
)
-
Moved by
Thursday, August 18, 2011 7:09 AM
Problem of misuse of the WITH clause (From:SQL Server Data Access)
-
Moved by
Answers
-
As others have mentioned, WITH only came into being with SQL Server 2005.
Prior to that, for simple subqueries just use a subquery in the FROM clause
;with temp_table(userid) as (Select distinct uid from tableOfRecords) select * from temp_table -- reordered in 2000 as select * FROM (Select distinct uid from tableOfRecords) temp_table(userid)
Abstain for the game… yeah right! http://tvnz.co.nz/national-news/telecom-scraps-controversial-abstinence-campaign-4355295
-
Proposed as answer by
Gert-Jan Strik
Thursday, August 18, 2011 1:24 PM -
Marked as answer by
Kalman Toth
Tuesday, August 23, 2011 3:53 PM
-
Proposed as answer by
With inside with
This is ok
WITH CS AS (
SELECT 'ab' as a
)
SELECT * from cs
http://data.stackexchange.com/mathematics/revision/138658/170321/comment-partition-by-question
But, as soon as I wrap it with a second with,
with PS as (
WITH CS AS (
SELECT 'ab' as a
)
SELECT * from cs
) select * from PS
I get this error,
Incorrect syntax near the keyword 'WITH'. 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. Incorrect syntax near ')'.
With inside select
The following is ok
select ab.a from (
select a from (SELECT 'ab' as a) b
) ab
This is also runs
with cs as (SELECT 'ab' as a)
select a from cs
But, wrapping expression with another select, as this one proposes,
select a from (
with cs as (SELECT 'ab' as a)
select a from cs
) ab
fails again with the same error.
I do it in data.stackexchange and they report that they use MSSQL
If you’re trying to run some OPENJSON() code in SQL Server, but you’re getting error Msg 319, Level 15 “Incorrect syntax near the keyword ‘with’”, one possibility is that you really do have a syntax error.
But if you’ve check and double checked, and you’re convinced there’s no syntax error, it could actually be that the error is a side effect of having the wrong database compatibility level.
Normally you’d get error Msg 208, Level 16 “Invalid object name ‘OPENJSON’.” when using a database compatibility level of lower than 130, but in some cases, SQL Server finds a problem with the WITH clause first.
I encountered this error when running valid OPENJSON() code, but on a database where the compatibility level was only 120.
OPENJSON() is only available on databases with a compatibility level of 130 or greater.
When I checked my database compatibility level, I saw that it was 120. I immediately increased it to 150 and I no longer got the error.
Example of the Error
Here’s an example of code that produces this error when the database compatibility level is lower than 130.
DECLARE @json NVARCHAR(4000) = N'{
"pets" : {
"cats" : [
{ "id" : 1, "name" : "Fluffy", "sex" : "Female" },
{ "id" : 2, "name" : "Long Tail", "sex" : "Female" },
{ "id" : 3, "name" : "Scratch", "sex" : "Male" }
],
"dogs" : [
{ "id" : 1, "name" : "Fetch", "sex" : "Male" },
{ "id" : 2, "name" : "Fluffy", "sex" : "Male" },
{ "id" : 3, "name" : "Wag", "sex" : "Female" }
]
}
}'
SELECT *
FROM OPENJSON(@json, '$.pets.dogs')
WITH (
[id] int,
[name] varchar(60),
[sex] varchar(6)
);Result:
Msg 319, Level 15, State 2, Line 17 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.
Fix the Error
You can easily fix this error by changing the database’s compatibility level to 130 or higher.
-- Change compatibility level
ALTER DATABASE Pets
SET COMPATIBILITY_LEVEL = 150;
-- Check compatibility level
SELECT compatibility_level
FROM sys.databases
WHERE name = 'Pets';Result:
+-----------------------+ | compatibility_level | |-----------------------| | 150 | +-----------------------+
Alternatively, if you don’t want to change this, you could switch to a database that you know has a suitable compatibility level.
Hopefully this post helps someone out there that encounters the same error.
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