Msg 319, Level 15, State 1, "Incorrect syntax near the keyword 'with'."

Question

USE [db]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[Get_PageTree]           
(@parentId int)          
AS           
BEGIN  

CREATE TABLE [dbo].[#T_Prime_Page](
    [PageId] [int]  NULL,
    [Flag] [bit] NULL,
    [ParentId] [int]  NULL,
    [RootId] [int] NULL,
    [PageName] [nvarchar](1550) NULL,
    [PageTitle] [nvarchar](1550) NULL,
    [Live] [bit] NULL,
    [FileName] [nvarchar](1550) NULL,
    [UseLink] [bit] NULL,
    [Link] [nvarchar](1500) NULL,
    [Keywords] [ntext] NULL,
    [Description] [ntext] NULL,
    [PageBody] [ntext] NULL,
    [DisplayOrder] [int] NULL,
    [metaTitle] [varchar](1500) NULL,
    [metaDesc] [varchar](5000) NULL)

Insert Into #T_Prime_Page
Select *  From Prime_Page Where Live = 0

Update #T_Prime_Page Set ParentID = null Where Flag = 0

WITH Page_CTE  AS (
SELECT PageID, PageName, PageTitle, FileName, UseLink, Link, Description, ParentId,  DisplayOrder, 0 AS Level
FROM #T_Prime_Page 
WHERE ParentId is null and PageId = @parentId
UNION ALL
SELECT p.PageID, p.PageName, p.PageTitle, p.FileName, p.UseLink, p.Link, p.Description, p.ParentId, p.DisplayOrder, Level + 1
FROM #T_Prime_Page p
INNER JOIN Page_CTE pcte ON pcte.PageId = p.ParentId
)
SELECT * into #t_Page_CTE
FROM Page_CTE Where PageID <> @parentId Order by Level, DisplayOrder

SELECT * 
FROM #t_Page_CTE Where Level = 1 Order by Level, DisplayOrder

SELECT * 
FROM #t_Page_CTE Where Level = 2 Order by Level, DisplayOrder

End

GO

Hi I am getting Error : Msg 319, Level 15, State 1, Procedure Get_PageTree, Line 30 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.

Please help to create the procedure.Thanks in advance.

Answer 1

Put a semicolon (;) just before WITH.

;WITH Page_CTE  AS (

As the error message tells you, the previous statement should be terminated with a semicolon.