Re: Hierarchical data again and part I

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: EradicusMax (dwinter_at_attbi.com)
Date: 10/21/04 

Date: Thu, 21 Oct 2004 14:18:30 -0400

Also, one more thing, sorry, need to pass your @indent back and forth so
need to declare that parameter as output

CREATE Procedure mySPLoop(@Indent INT OUTPUT,@parentID INT) AS

and in your call to the sp in the sp

EXEC mySPLoop @Indent OUTPUT, @locationID

"Christian Perthen" <abracadabara@dontreplytothisaddress.com> wrote in
message news:ukcKJC5tEHA.2128@TK2MSFTNGP11.phx.gbl...
> EradicusMax,
>
> Thanks for your input.
> However, it doesn't seem to work for me.
> When troubleshooting it, I ran the mySPLoop, with the "insert int
> #tempTable..." commented out.
> I get the error message "A cursor with the name 'myCur' already exists."
> even though is is deallocated.
> Any idea how to by-pass this error.
>
> Thanks
> Christian
>
>
> "EradicusMax" <dwinter@attbi.com> wrote in message
> news:%239I9NdytEHA.908@TK2MSFTNGP11.phx.gbl...
> > Depends on your definition of easily. Reading the article, it does what
I
> > expected, calling a cursor that calls itself to implement a tree
> structure.
> > I use this methodology to build entire pages, but mostly treed items
such
> as
> > a menue that is totally configurable in the database. They can get quite
> > complicated at times when looking at the overall code, but lets look at
it
> > in very simple terms. Looking at your results, it appears you are really
> > only using the table location table, but even if you are not, let just
> look
> > at that table.
> > In this table you have a locationID and a parentID. Your starting point
is
> > parentID=0 so you will need to create a cursor to pull just those
records
> > with a certain parentID. That cursor will need to pull items with it's
> > locationID as parentID, etc.. You also need an indent level, lets call
> that
> > Indent starting at 0, and a place to store your results, which we will
use
> a
> > temp table for our example, and the other variables for use in the
cursor
> >
> > here is your initial query, which will call a stored proc calling
itself.
> >
> > DECLARE @Indent INT, @locationID INT
> > SET @Indent=0
> > SET @locationID=0 --This is our starting point
> > --a table just to store things off
> > CREATE TABLE #TempTable(Indent INT, Name VARCHAR(50), LocationID INT)
> > --This is the sp that will loop until done, see the code below this
> > EXEC mySPLoop @Indent, @locationID
> > --look at our data
> > SELECT * FROM #TempTable
> > DROP TABLE #TempTable
> >
> > this is the store proc
> > CREATE Procedure mySPLoop(@Indent INT,@parentID INT) AS
> > DECLARE @locationID INT, @Name VARCHAR(50)
> > DECLARE myCur CURSOR FOR
> > SELECT locationID,name FROM LocationTable WHERE parentID = @parentID
> > OPEN myCur
> > WHILE 1=1
> > BEGIN --unconventional cursor methodology, but simplifying things
> > FETCH myCur INTO @locationID, @Name
> > IF @@FETCH_STATUS!=0 BREAK
> > INSERT INTO #TempTable(Indent,Name,LocationID)
> > VALUES(@Indent,@Name,@LocationID)
> > --increase the indent
> > SELECT @Indent=@Indent+1
> > --let the sp call itself
> > EXEC mySPLoop @Indent, @locationID
> > END
> > CLOSE myCur
> > DEALLOCATE myCur
> > --decrease the indent
> > SELECT @Indent=@Indent-1
> >
> > That's about it in a nutshell. The cursor calls itself, passing in the
> > parentID of the next group to get. As the sp calls itself, the indents
> > increase, but once there is no more data, the indent is decrease as it
> exits
> > each call.
> > Now you could pass the #TempTable back to the web as a recordset and
build
> > your select object that way, or you could build the html text inside the
> > stored proc, and pass back just a single value which contains all the
> option
> > text formatted the way you want by making some mods to the above, your
> > choice.
> >
> >
> > "Christian Perthen" <abracadabara@dontreplytothisaddress.com> wrote in
> > message news:uefIdZxtEHA.2596@TK2MSFTNGP10.phx.gbl...
> > > Hi,
> > >
> > > I read an earlier posting about hierarchical data which is a new area
> for
> > > me.
> > > I found Vyas article "Working with hierarchical data in SQL Server
> > > databases"
> > > closely resembles the solution of my problem but unfortunately his
> example
> > > doesn't generate any result for me.
> > > I did several approaches on the client side but non yield the right
> > result.
> > >
> > > My problem, very simplified, is broken into 2 tables its result below.
> > >
> > > table location
> > > locationID, parentID, name
> > > 1, 0, Americas
> > > 2, 0, Europe
> > > 3, 1, N.America
> > > 4, 1, S.America
> > > 5, 3, USA
> > > 6, 3, Canada
> > > 7, 2, UK
> > > 8, 4, Brazil
> > >
> > > table item
> > > itemID, locationID, name
> > > 1, 5, Pike
> > > 2, 7, Cod
> > > 3, 5, Bass
> > > 4, 6, Grayling
> > >
> > > The result, to be used in a <select> menu, is filtered by table item
> and
> > > need to include indented ids.
> > > indent, name, locationID
> > > 0, America, 1
> > > 1, N. America, 3
> > > 2, Canada, 6
> > > 2, USA, 5
> > > 0, Europe, 2
> > > 1, UK, 7
> > >
> > > Any idea how this can be easly resolved?
> > >
> > > Thanks in advance
> > > Christian
> > >
> > >
> >
> >
>
>

Relevant Pages