?
Solved

SQL Server 2008 stored procedure

Posted on 2011-03-09
10
Medium Priority
?
268 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
  • 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…

862 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