stumped with stored proc and temp tables

[davinski]

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.

CREATE                          PROC dbo.ShowHierarchyForum
(
	@Root int,
	@ArticleId int
)
AS
BEGIN

if not exists (select name from [tempdb].[dbo].[sysobjects] where name like '#YourLocalTempTable%')  
create table #YourLocalTempTable (Id int, ParentId int, ArticleId int)


	SET NOCOUNT ON
	DECLARE @CID int, @PID int

	insert into #YourLocalTempTable SELECT CP_FORUM_Comments.Id , ParentId ,ArticleId from CP_FORUM_Comments WHERE ID = @Root and ArticleId = @ArticleId 

	SET @CID = (SELECT MAX(ID) FROM CP_FORUM_Comments  WHERE ParentID = @Root)
	
	WHILE @CID IS NOT NULL
	BEGIN
		EXEC dbo.ShowHierarchyForum @CID, @ArticleId
		SET @CID = (SELECT MAX(ID) FROM CP_FORUM_Comments  WHERE ParentID = @Root AND ID < @CID and ArticleId = @ArticleId)


	END
END

if @@NESTLEVEL =1 
select * from #YourLocalTempTable
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

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.

exec ShowHierarchyForum 0, 1

Many thanks

Davinski

[jagged]

At first glance, looks like changing max(id) and id < @cid to min(id) and id > @cid should work

[davinski]

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

[davinski]

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

23         SET @CID = (SELECT MAX(ID) FROM CP_FORUM_Comments  WHERE ParentID = @Root AND ID < @CID and ArticleId = @ArticleId)

So I changed it to

23         SET @CID = (SELECT MIN(ID) FROM CP_FORUM_Comments  WHERE ParentID = @Root AND ID > @CID and ArticleId = @ArticleId)

without success, any other ideas?

Thanks

Davinski

[davinski]

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