Stored Proc Help... - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Stored Proc Help...

Author

Topic

jpclaude
Starting Member

6 Posts

Posted - 2004-03-19 : 14:10:52

Hi. I've got a stored proc that returns the full category & subcategory list when entering the final category id. If I enter the ID of a 3rd-level subcategory, then I'll get the main category, sub-category, and the 3rd category all returned to the user.

I've been trying to reverse the proc to I can enter a main category ID and return a list of all its sub categories. I'm not sure if I'm over looking something.

Here's the original stored proc that I'd like to reverse.

------------------------------------------------------------

CREATE PROCEDURE sp_get_modelcat_path
@intCategory INT,
@vchrPath VARCHAR(200) = '' OUTPUT

AS

-- DECLARE local variables.
DECLARE @intCatParent INT

-- RETRIEVE the given category's parent category.
SELECT @intCatParent = intCatParent, @vchrCatPath = vchrName FROM tblCategories WHERE intID = @intCategory

-- CONCATENATE the successive parent categories all the way to the top of the category hierarchy.
WHILE NOT @intCatParent = 0

BEGIN

SELECT @intCatParent = intCatParent, @vchrPath = vchrName + ',' + @vchrPath FROM tblCategories WHERE intID= @intCatParent

-- TERMINATE the loop if the category appears a one of its own ancestors (an infinite loop will occur otherwise, if this is the case).
IF @intCatParent = @intCategory SET @intCatParent = 0

END
PRINT @vchrPath

GO

------------------------------------------------------------

Thanks,

JpC

jpclaude
Starting Member

6 Posts

Posted - 2004-03-19 : 14:30:50

Ok, I managed to get it working right but the results appear on the same line. How can I get the next result to appear on the next row instead of them all being on the same row?

JpC

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2004-03-19 : 14:31:14

There are a variety of ways to deal with trees or hierarchies. It appears you are working with a typical adjacency model. You'll find several articles on this site by searching for tree on the site: http://www.sqlteam.com/searchresults.asp?SearchTerms=tree

--------------------------------------------------------------
Find more words of wisdom at [url]http://weblogs.sqlteam.com/markc[/url]

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2004-03-21 : 11:24:29

Not sure if this will help you but this is how I do it:

DECLARE @myTable table 
  (CatID int, Category varchar(30), SubCatID int)

INSERT INTO @myTable
SELECT 1, 'Football', NULL UNION ALL
SELECT 2, 'England', 1 UNION ALL
SELECT 3, 'Premier League', 2 UNION ALL
SELECT 4, '1. Div', 2 UNION ALL
SELECT 5, '2. Div', 2 UNION ALL
SELECT 6, 'Norway', 1 UNION ALL
SELECT 7, 'Tippeligaen', 6

SELECT a.CatID AS CatIDA, a.Category AS A, b.CatID AS CatIDB, b.Category AS B, 
  c.CatID AS CatIDC, c.Category AS C
FROM @myTable a INNER JOIN @myTable b
    ON b.SubCatID = a.CatID 
  INNER JOIN @myTable c
    ON c.SubCatID = b.CatID
WHERE a.SubCatID IS NULL

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources