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 get an error message: Msg 319, Level 15, State 1, Get_PageTree procedure, line 30 Incorrect syntax next to the 'with' keyword. If this statement is a common table expression, an xmlnamespaces clause, or a change tracking context clause, the previous statement must end with a semicolon.
Please help create the procedure. Thanks in advance.