Solved

SQL Server 2008 stored procedure

Posted on 2011-03-09
10
258 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 40

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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
LVL 40

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 40

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 40

Accepted Solution

by:
Sharath earned 500 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
In this article I will describe the Copy Database Wizard 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 video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

861 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