469,643 Members | 1,698 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,643 developers. It's quick & easy.

stumped with stored proc and temp tables

Hello, it's been a while since my last post, hope everyone is fine :P

I'm stuck with what seems to be a simple task, but I'm getting confused on how to complete this. Basically, I have been given a stored procedure which nests itself within itself and uses a temporary table to store the data while writing.

The nested stored procedure is used so that it can output the data rows in a db table into an organized tree like

Example 1

1.0
__1.1
__1.2
2.0
__2.1
__2.2
__2.3
etc...

Problem is, the nested stored proc is outputting the data in REVERSE order at the moment,

Example 2

2.0
__2.3
__2.2
__2.1
1.0
__1.2
__1.1


The table design is a the simple TreeView design concept, with a main ID column and the ParentID column representing the ID of it's parent.

The Stored Procedure will be used as part of a Forum Control, therefore ArticleId defining the Post that the user has selected to view.

I include the SQL Script for creating the Stored Proc below.

Expand|Select|Wrap|Line Numbers
  1.  
  2. CREATE                          PROC dbo.ShowHierarchyForum
  3. (
  4.     @Root int,
  5.     @ArticleId int
  6. )
  7. AS
  8. BEGIN
  9.  
  10. if not exists (select name from [tempdb].[dbo].[sysobjects] where name like '#YourLocalTempTable%')  
  11. create table #YourLocalTempTable (Id int, ParentId int, ArticleId int)
  12.  
  13.  
  14.     SET NOCOUNT ON
  15.     DECLARE @CID int, @PID int
  16.  
  17.     insert into #YourLocalTempTable SELECT CP_FORUM_Comments.Id , ParentId ,ArticleId from CP_FORUM_Comments WHERE ID = @Root and ArticleId = @ArticleId 
  18.  
  19.     SET @CID = (SELECT MAX(ID) FROM CP_FORUM_Comments  WHERE ParentID = @Root)
  20.  
  21.     WHILE @CID IS NOT NULL
  22.     BEGIN
  23.         EXEC dbo.ShowHierarchyForum @CID, @ArticleId
  24.         SET @CID = (SELECT MAX(ID) FROM CP_FORUM_Comments  WHERE ParentID = @Root AND ID < @CID and ArticleId = @ArticleId)
  25.  
  26.  
  27.     END
  28. END
  29.  
  30. if @@NESTLEVEL =1 
  31. select * from #YourLocalTempTable
  32. GO
  33. SET QUOTED_IDENTIFIER OFF 
  34. GO
  35. SET ANSI_NULLS ON 
  36. GO
  37.  
  38.  
  39.  
Can anyone please show me how to manipulate this so that the stored procedure outputs the data in the correct order as in Example 1 please

Just as a note, when executing this stored proc, we pass a 0 value to Root, e.g.
Expand|Select|Wrap|Line Numbers
  1. exec ShowHierarchyForum 0, 1
  2.  
Many thanks

Davinski
Feb 23 '08 #1
4 1482
jagged
23
At first glance, looks like changing max(id) and id < @cid to min(id) and id > @cid should work
Feb 23 '08 #2
At first glance, looks like changing max(id) and id < @cid to min(id) and id > @cid should work
Thanks your response Jagged,

I can't see where you mean to change the code, which line do you mean exactly please, I'm a real noob with nested stored procs and temp tables.

Thanks

Davinski
Feb 23 '08 #3
Thanks your response Jagged,

I can't see where you mean to change the code, which line do you mean exactly please, I'm a real noob with nested stored procs and temp tables.

Thanks

Davinski
The only part where I think you mean is line 23 in the original post

Expand|Select|Wrap|Line Numbers
  1.  
  2. 23         SET @CID = (SELECT MAX(ID) FROM CP_FORUM_Comments  WHERE ParentID = @Root AND ID < @CID and ArticleId = @ArticleId)
  3.  
  4.  
So I changed it to

Expand|Select|Wrap|Line Numbers
  1.  
  2. 23         SET @CID = (SELECT MIN(ID) FROM CP_FORUM_Comments  WHERE ParentID = @Root AND ID > @CID and ArticleId = @ArticleId)
  3.  
  4.  
without success, any other ideas?

Thanks

Davinski
Feb 23 '08 #4
At first glance, looks like changing max(id) and id < @cid to min(id) and id > @cid should work
Right, Jagged, GOT IT

Changed line 18, 'MAX' to 'MIN', and of course, line 23 as previously stated in my previous post and it now outputs the data the way needed.

Many thanks Jagged,

Davinski
Feb 23 '08 #5

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

8 posts views Thread by C Kirby | last post: by
9 posts views Thread by Wolfgang Kreuzer | last post: by
3 posts views Thread by mandible | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.