SQL Server 2008 stored procedure

I am wondering how I can write a stored procedure that returns strings based on category keywords.  The example search below returns the categoryID of the last category in the list and the whole string.  Below are 3 examples.

The results should look like this.

"Causes / International / Cancer", ACD502
"Art / International / Painting", ACD536
"Countries / International", ACD 5363


Keyword Query
SELECT name FROM Categories
WHERE Name LIKE "Interational"
 
Categories Table Columns
  CategoryID
  Name
  Parent
 
Thanks in advance.
SFTProdAsked:
Who is Participating?
 
SharathConnect With a Mentor Data EngineerCommented:
like this.
DECLARE  @table  TABLE( 
                       CategoryID VARCHAR(20), 
                             Name VARCHAR(40), 
                         ParentID VARCHAR(20) 
                       ) 
DECLARE @Name varchar(20) = 'International'
INSERT @table 
VALUES('ACD414','International','ACD119'), 
      ('ACD119','Television','ACD236'), 
      ('ACD236','Film','ACD253'), 
      ('ACD253','Entertainment','ACD23'), 
      ('ACD23','Books & Literature','ACD1'), 
      ('ACD1','Art, Design & culture',NULL), 
      ('ACD199','International','ACD3'), 
      ('ACD3','Causes',NULL) 

;WITH RecCTE 
     AS (SELECT CategoryID, Name, ParentID, 
                CAST(Name AS VARCHAR(MAX)) AS Tree, 
                1                          depth, 
                CategoryID                 TopParentID 
           FROM @table 
          WHERE ParentID IS NULL
         UNION ALL 
         SELECT c.CategoryID, c.Name, c.ParentID, 
                p.Tree + ' / ' + CAST(c.Name AS VARCHAR(MAX)), 
                p.depth + 1, 
                p.TopParentID 
           FROM @table c 
                JOIN RecCTE p 
                  ON c.ParentID = p.CategoryID) 
SELECT Tree, 
       CategoryID 
  FROM (SELECT *, 
               ROW_NUMBER() 
                 OVER(PARTITION BY TopParentID ORDER BY depth DESC) rn 
          FROM RecCTE) t1 
 WHERE rn = 1 and Tree like '%'+@Name+'%'
/*
Tree	CategoryID
Art, Design & culture / Books & Literature / Entertainment / Film / Television / International	ACD414
Causes / Interational	ACD199
*/

Open in new window

0
 
SharathData EngineerCommented:
Try this query.
;WITH cte 
     AS (SELECT *, 
                LTRIM(SUBSTRING(Name,n,CHARINDEX('/',Name + '/',n) - n)) AS CategoryName 
           FROM Categories 
                CROSS JOIN (SELECT NUMBER 
                              FROM MASTER..spt_values 
                             WHERE TYPE = 'P') AS Numbers(n) 
          WHERE SUBSTRING('/' + Name,n,1) = '/' 
                AND n < LEN(Name) + 1 
                AND Name LIKE '%International%'), 
     cte1 
     AS (SELECT *, 
                ROW_NUMBER() 
                  OVER(PARTITION BY CategoryID ORDER BY n DESC) rn 
           FROM cte) 
SELECT CategoryID, 
       Name, 
       Parent, 
       CategoryName 
  FROM cte1 
 WHERE rn = 1

Open in new window

You can create and SP and pass CategoryName as parameter.
CREATE PROCEDURE SP_CATEGORY 
                @CategoryName VARCHAR(100) 
AS 
  ;WITH cte 
       AS (SELECT *, 
                  LTRIM(SUBSTRING(Name,n,CHARINDEX('/',Name + '/',n) - n)) AS CategoryName 
             FROM Categories 
                  CROSS JOIN (SELECT NUMBER 
                                FROM MASTER..spt_values 
                               WHERE TYPE = 'P') AS Numbers(n) 
            WHERE SUBSTRING('/' + Name,n,1) = '/' 
                  AND n < LEN(Name) + 1 
                  AND Name LIKE '%' + @CategoryName + '%'), 
       cte1 
       AS (SELECT *, 
                  ROW_NUMBER() 
                    OVER(PARTITION BY CategoryID ORDER BY n DESC) rn 
             FROM cte) 
  SELECT CategoryID, 
         Name, 
         Parent, 
         CategoryName 
    FROM cte1 
   WHERE rn = 1

Open in new window

Here is the output with your sample.
DECLARE  @Categories  TABLE( 
                            CategoryID INT, 
                                  Name VARCHAR(100), 
                                Parent VARCHAR(20) 
                            ) 

INSERT @Categories 
VALUES(1,'Causes / International / Cancer','ACD502'), 
      (2,'Art / International / Painting','ACD536'), 
      (3,'Countries / International','ACD 5363'); 

;WITH cte 
     AS (SELECT *, 
                LTRIM(SUBSTRING(Name,n,CHARINDEX('/',Name + '/',n) - n)) AS CategoryName 
           FROM @Categories 
                CROSS JOIN (SELECT NUMBER 
                              FROM MASTER..spt_values 
                             WHERE TYPE = 'P') AS Numbers(n) 
          WHERE SUBSTRING('/' + Name,n,1) = '/' 
                AND n < LEN(Name) + 1 
                AND Name LIKE '%International%'), 
     cte1 
     AS (SELECT *, 
                ROW_NUMBER() 
                  OVER(PARTITION BY CategoryID ORDER BY n DESC) rn 
           FROM cte) 
SELECT CategoryID, 
       Name, 
       Parent, 
       CategoryName 
  FROM cte1 
 WHERE rn = 1
/*
CategoryID	Name	Parent	CategoryName
1	Causes / International / Cancer	ACD502	Cancer
2	Art / International / Painting	ACD536	Painting
3	Countries / International	ACD 5363	International
*/

Open in new window

0
 
SFTProdAuthor Commented:
I have not had a chance to try this yet.  I will update you when I try it.

Thanks.
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
SFTProdAuthor Commented:
Hi There.

The stored procedure you provided seems to reutnr the same results as the following.

SELECT * FROM ActivityCategoryDefinitions
WHERE Name LIKE @categoryName

This is one step closer to what I'm looking for but I need it to traverse through the top and return a single string along with the categoryID of the last element of the string.

Here is a better sample of what I'm trying to do.


Example Results

Record 1: "Art, Design & Culture \ Books & Literature \ Entertainment \ Film \ Television \ International"      ACD414
Record 2: "Causes \ International" ACD199

Example Database Records
CategoryID                           Name             ParentID
ACD414            International            ACD119
ACD119            Television            ACD236
ACD236            Film            ACD253
ACD253            Entertainment      ACD23
ACD23            Books & Literature      ACD1
ACD1            Art, Design & culture      NULL
ACD199            Interational            ACD3
ACD3            Causes            NULL

Thanks in advance.
0
 
SharathData EngineerCommented:
try this query.
;WITH RecCTE 
     AS (SELECT CategoryID, Name, ParentID, 
                CAST(Name AS VARCHAR(MAX)) AS Tree, 
                1                          depth, 
                CategoryID                 TopParentID 
           FROM your_table 
          WHERE ParentID IS NULL 
         UNION ALL 
         SELECT c.CategoryID, c.Name, c.ParentID, 
                p.Tree + ' / ' + CAST(c.Name AS VARCHAR(MAX)), 
                p.depth + 1, 
                p.TopParentID 
           FROM your_table c 
                JOIN RecCTE p 
                  ON c.ParentID = p.CategoryID) 
SELECT Tree, 
       CategoryID 
  FROM (SELECT *, 
               ROW_NUMBER() 
                 OVER(PARTITION BY TopParentID ORDER BY depth DESC) rn 
          FROM RecCTE) t1 
 WHERE rn = 1

Open in new window

Tested with your sample.
DECLARE  @table  TABLE( 
                       CategoryID VARCHAR(20), 
                             Name VARCHAR(40), 
                         ParentID VARCHAR(20) 
                       ) 

INSERT @table 
VALUES('ACD414','International','ACD119'), 
      ('ACD119','Television','ACD236'), 
      ('ACD236','Film','ACD253'), 
      ('ACD253','Entertainment','ACD23'), 
      ('ACD23','Books & Literature','ACD1'), 
      ('ACD1','Art, Design & culture',NULL), 
      ('ACD199','Interational','ACD3'), 
      ('ACD3','Causes',NULL) 

;WITH RecCTE 
     AS (SELECT CategoryID, Name, ParentID, 
                CAST(Name AS VARCHAR(MAX)) AS Tree, 
                1                          depth, 
                CategoryID                 TopParentID 
           FROM @table 
          WHERE ParentID IS NULL 
         UNION ALL 
         SELECT c.CategoryID, c.Name, c.ParentID, 
                p.Tree + ' / ' + CAST(c.Name AS VARCHAR(MAX)), 
                p.depth + 1, 
                p.TopParentID 
           FROM @table c 
                JOIN RecCTE p 
                  ON c.ParentID = p.CategoryID) 
SELECT Tree, 
       CategoryID 
  FROM (SELECT *, 
               ROW_NUMBER() 
                 OVER(PARTITION BY TopParentID ORDER BY depth DESC) rn 
          FROM RecCTE) t1 
 WHERE rn = 1
/*
Tree	CategoryID
Art, Design & culture / Books & Literature / Entertainment / Film / Television / International	ACD414
Causes / Interational	ACD199
*/

Open in new window

0
 
SharathData EngineerCommented:
replace / with \ if you want back slash as delimeter.
;WITH RecCTE 
     AS (SELECT CategoryID, Name, ParentID, 
                CAST(Name AS VARCHAR(MAX)) AS Tree, 
                1                          depth, 
                CategoryID                 TopParentID 
           FROM your_table 
          WHERE ParentID IS NULL 
         UNION ALL 
         SELECT c.CategoryID, c.Name, c.ParentID, 
                p.Tree + ' \ ' + CAST(c.Name AS VARCHAR(MAX)), 
                p.depth + 1, 
                p.TopParentID 
           FROM your_table c 
                JOIN RecCTE p 
                  ON c.ParentID = p.CategoryID) 
SELECT Tree, 
       CategoryID 
  FROM (SELECT *, 
               ROW_NUMBER() 
                 OVER(PARTITION BY TopParentID ORDER BY depth DESC) rn 
          FROM RecCTE) t1 
 WHERE rn = 1

Open in new window

0
 
SFTProdAuthor Commented:
Where would I put in the CategoryName criteria so it returns the tree that matches that name?

Thanks.
0
 
SFTProdAuthor Commented:
It works well for "International" but when I put in "Entertainment."  

I expect it to show "Art, Design & culture / Books & Literature / Entertainment"  ACD23

Instead, it shows "Art, Design & culture / Books & Literature / Entertainment / Film / Television / International" ACD414
0
 
SFTProdConnect With a Mentor Author Commented:
I ended up using a while loop and traversing through the query with the concatenating operator to get the string I was trying to build.

Thanks for the help though.
0
 
SFTProdAuthor Commented:
Solution was not accurate.  I ended up figuring it out myself.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.