?
Solved

SQL Server 2008 stored procedure

Posted on 2011-03-09
10
Medium Priority
?
264 Views
Last Modified: 2012-06-27
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.
0
Comment
Question by:SFTProd
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
10 Comments
 
LVL 41

Expert Comment

by:Sharath
ID: 35092996
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
 

Author Comment

by:SFTProd
ID: 35123302
I have not had a chance to try this yet.  I will update you when I try it.

Thanks.
0
 

Author Comment

by:SFTProd
ID: 35124144
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
Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

 
LVL 41

Expert Comment

by:Sharath
ID: 35135707
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
 
LVL 41

Expert Comment

by:Sharath
ID: 35135715
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
 

Author Comment

by:SFTProd
ID: 35155054
Where would I put in the CategoryName criteria so it returns the tree that matches that name?

Thanks.
0
 
LVL 41

Accepted Solution

by:
Sharath earned 2000 total points
ID: 35163075
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
 

Author Comment

by:SFTProd
ID: 35170186
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
 

Assisted Solution

by:SFTProd
SFTProd earned 0 total points
ID: 35202567
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
 

Author Closing Comment

by:SFTProd
ID: 35230128
Solution was not accurate.  I ended up figuring it out myself.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question