hierarchies - 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

SQL Server Development (2000)

hierarchies

Author

Topic

sgebbie
Starting Member

7 Posts

Posted - 2002-06-23 : 20:23:02

Like a lot of others I am trying to perform hierachical functions.
Books online has this example which does it no problem, except it doesnt count the records and I cant manage to modify it or count the records in any way.

If anyone knows how to count the responses from this procedure, it would be a great help, it can then be tailored to suit.

Parent | Child
--------------| ----------------------------------
World | Europe
World | North America
Europe | France
France | Paris
North America | United States
North America | Canada
United States | New York
United States | Washington
New York | New York City
Washington | Redmond

CREATE PROCEDURE expand (@current char(20)) as
SET NOCOUNT ON
DECLARE @level int, @line char(20)
CREATE TABLE #stack (item char(20), level int)
INSERT INTO #stack VALUES (@current, 1)
SELECT @level = 1

WHILE @level > 0
BEGIN
IF EXISTS (SELECT * FROM #stack WHERE level = @level)
BEGIN
SELECT @current = item
FROM #stack
WHERE level = @level
SELECT @line = space(@level - 1) + @current
PRINT @line
DELETE FROM #stack
WHERE level = @level
AND item = @current
INSERT #stack
SELECT child, @level + 1
FROM hierarchy
WHERE parent = @current
IF @@ROWCOUNT > 0
SELECT @level = @level + 1
END
ELSE
SELECT @level = @level - 1
END -- WHILE

thanks

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-06-24 : 13:00:17

A lot of thread reads, but no response usually indicates some confusion. What exactly do you want to count?

<O>

sgebbie
Starting Member

7 Posts

Posted - 2002-06-24 : 13:11:49

Hi thanks
essentially if I can manage to store the results of this query in another table, then I should be able to do everything I need.

thhe problem is if I execute this from asp I connot access the fields
objRecordset.fields = 0. even when you run it from query analyzer there are no column names?? never seen this before

So if it can be modified to store the same results in another table that would be excellent (as long as they had a column name). Otherwise if could figure out how to access the results from
asp I can just set up a counter in there.

I am completly baffled by this sp.

sgebbie
Starting Member

7 Posts

Posted - 2002-06-24 : 13:43:00

WooHoo!
As far as i can see I have done it, but since I didnt understand it before I have no idea if it is going to cause the server to explode if its ran with a large amount of records.
If anyone could understand this, can you tell me if this is ok.
It may not be pretty but it does what i want, so as long as it wont kill the server i'm happy, and anyone else can modify this to suit their hierarchical functions

so here' tis

CREATE PROCEDURE expand (@current char(20)) as
SET NOCOUNT ON
DECLARE @level int, @line char(20)
CREATE TABLE #stack (item char(20), level int)
create table #mytemp (country char(20))

INSERT INTO #stack VALUES (@current, 1)
SELECT @level = 1

WHILE @level > 0
BEGIN
IF EXISTS (
SELECT * FROM #stack WHERE level = @level)
BEGIN

SELECT @current = item
FROM #stack
WHERE level = @level
SELECT @line = space(@level - 1) + @current
PRINT @line
DELETE FROM #stack
WHERE level = @level
AND item = @current
INSERT #stack
SELECT child, @level + 1
FROM table1
WHERE parent = @current
IF @@ROWCOUNT > 0
SELECT @level = @level + 1
insert into #mytemp values (@current )
END
ELSE
SELECT @level = @level - 1
END -- WHILE

select * from #mytemp
drop table #mytemp
GO

then in asp
while not objRs.Eof
Response.Write objRs.Fields("Country").Value
objRs.MoveNext
wend

Will the server explode??
thanks guys.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-06-24 : 14:22:58

Your SQL code is perfect, but this bit of ASP code:

while not objRs.Eof
Response.Write objRs.Fields("Country").Value
objRs.MoveNext
wend

...could time out your web server. I'd recommend using GetRows to populate the recordset into an array. It's very fast, and once it's in an array you can close the connection and reference the array instead. You can also try using GetString, it will create a string of the recordset instead of an array, but it's a little less flexible. Here's some links with examples on how to use it:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=11019
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=6501

Make sure to read any associated links in those threads, they lead to better examples and more in-depth discussion on the benefits.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources