[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

SQL Server 2008 stored procedure

Posted on 2011-03-09
10
Medium Priority
?
265 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

649 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