Sign in Subscribe
SQL Server

Product Categories and TSQL

Tim Wappat

• 1 min read

I’ve just spent a few hours this morning working on a drop down menu for the website. It works a little like the Microsoft Vista file browser address bar. Any part of the breadcrumb type trail can be clicked on to drop down the other categories at that level.

Since I’ve upgraded the website SQL server earlier in the year from 2000 to 2008 version of SQL server I now can utilise Common Table Expressions to get the solution (CTE). Used in a recursive manner you can do all sorts that needed cursors before – great stuff!

Here was my resulting script before packaging into a  stored procedure:

 

-- Script to get all the categories from a given category up over 
-- includes a mark against the path we are on and other categories at that level.
-- T.WAPPAT 20th July 2009
---
DECLARE @CatID Integer
SET @CatID=22141
WITH CategoryParents(CatID, ParentCatID, SortInLevel, 
                            CatLangCode, CatDesc, InPath,  Depth)
AS
(
--Anchor member
SELECT    CategoriesSibs.CatID as CatID, CategoriesSibs.ParentCatID,
        CategoriesSibs.SortInLevel, CategoriesDesc.CatLangCode, 
        CategoriesDesc.CatDesc, 
        CASE CategoriesSibs.CatID WHEN @CatID 
            THEN 1 ELSE 0 END as InPath,
        0 as Depth
FROM  MA_Categories AS Categories WITH (NOLOCK)   
      JOIN 
      MA_Categories as CategoriesChild WITH (NOLOCK) 
        ON CategoriesChild.ParentCatID=Categories.CatID
      JOIN 
      MA_Categories as CategoriesSibs WITH (NOLOCK) 
        ON CategoriesSibs.ParentCatID=Categories.CatID
      JOIN
      MA_CategoryDescs AS CategoriesDesc WITH (NOLOCK) 
        ON CategoriesDesc.CatID = CategoriesSibs.CatID 
WHERE [email protected]
UNION ALL
--Recursive member
select  Categories.CatID, Categories.ParentCatID, 
        CategoriesSibs.SortInLevel, CategoriesDesc.CatLangCode,
        CategoriesDesc.CatDesc, 
        CASE CategoriesSibs.CatID WHEN CategoryPArents.ParentCatID 
            THEN 1 ELSE 0 END as InPath,
        Depth+1 as Depth
FROM         CategoryParents AS CategoryParents 
             JOIN 
             MA_Categories AS Categories WITH (NOLOCK) 
                ON CategoryParents.ParentCatID=Categories.CatID  
             JOIN
             MA_Categories as CategoriesSibs WITH (NOLOCK) 
                ON CategoriesSibs.ParentCatID=Categories.ParentCatID
                JOIN
             MA_CategoryDescs AS CategoriesDesc WITH (NOLOCK) 
                ON CategoriesDesc.CatID = CategoriesSibs.CatID  
)
-- execute CTE
select  distinct * from CategoryParents  ORDER BY Depth, SortinLevel

 

You may find this useful to hack around for your own purposes. The following are table definitions.

CREATE TABLE [dbo].[MA_Categories](
    [CatID] [int] NULL,
    [ParentCatID] [int] NULL,
    [SortInLevel] [int] NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[MA_CategoryDescs](
    [CatID] [int] NULL,
    [CatLangCode] [varchar](3) NULL,
    [CatDesc] [varchar](200) NULL
) ON [PRIMARY]