CREATE TABLE [dbo].[NewsCategories](
[CategoryID] [int] IDENTITY(1,1) NOT NULL,
[CategoryName] [nvarchar](100) NULL,
[ZOrder] [int] NULL,
[ParentID] [int] NULL,
[IsChecked] [bit] NULL,
[LangID] [int] NULL,
[IsChild] [varchar](50) NULL,
[Position] [int] NOT NULL,
CONSTRAINT [PK_EQ_NewsCategories] PRIMARY KEY CLUSTERED
(
[CategoryID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER PROC SP_GetAllCategory
AS
DECLARE @Categories TABLE (
ID int PRIMARY KEY ,Title nvarchar(256) ,ParentID int )
INSERT INTO @Categories select CategoryID,CategoryName,ParentID from NewsCategories;
WITH CategoryTree AS (SELECT r.ID, r.Title, 0 Level, r.ParentID,
CAST(r.Title AS nVARCHAR(1000)) AS ''Path'' FROM @Categories r
WHERE r.ParentID = 0 UNION ALL
SELECT c.ID, c.Title, p.Level + 1 AS Level, c.ParentID,
CAST((p.path + ''/'' + c.Title) AS nVARCHAR(1000)) AS ''Path''
FROM @Categories c INNER JOIN CategoryTree p
ON p.ID = c.ParentID )
SELECT ID AS CategoryID, REPLICATE('' --'', Level) + Title AS CategoryName, [Path] FROM CategoryTree ORDER BY [Path]