| Groups | Blog | Home |
sql server programming : Hierarchical data again and part I
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
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... [quoted text, click to view] > 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 > >
Christian
See Itzik Ben-Gan's example IF object_id('dbo.Employees') IS NOT NULL DROP TABLE Employees GO IF object_id('dbo.ufn_GetSubtree') IS NOT NULL DROP FUNCTION dbo.ufn_GetSubtree GO CREATE TABLE Employees ( empid int NOT NULL, mgrid int NULL, empname varchar(25) NOT NULL, salary money NOT NULL, CONSTRAINT PK_Employees_empid PRIMARY KEY(empid), CONSTRAINT FK_Employees_mgrid_empid FOREIGN KEY(mgrid) REFERENCES Employees(empid) ) CREATE INDEX idx_nci_mgrid ON Employees(mgrid) INSERT INTO Employees VALUES(1 , NULL, 'Nancy' , $10000.00) INSERT INTO Employees VALUES(2 , 1 , 'Andrew' , $5000.00) INSERT INTO Employees VALUES(3 , 1 , 'Janet' , $5000.00) INSERT INTO Employees VALUES(4 , 1 , 'Margaret', $5000.00) INSERT INTO Employees VALUES(5 , 2 , 'Steven' , $2500.00) INSERT INTO Employees VALUES(6 , 2 , 'Michael' , $2500.00) INSERT INTO Employees VALUES(7 , 3 , 'Robert' , $2500.00) INSERT INTO Employees VALUES(8 , 3 , 'Laura' , $2500.00) INSERT INTO Employees VALUES(9 , 3 , 'Ann' , $2500.00) INSERT INTO Employees VALUES(10, 4 , 'Ina' , $2500.00) INSERT INTO Employees VALUES(11, 7 , 'David' , $2000.00) INSERT INTO Employees VALUES(12, 7 , 'Ron' , $2000.00) INSERT INTO Employees VALUES(13, 7 , 'Dan' , $2000.00) INSERT INTO Employees VALUES(14, 11 , 'James' , $1500.00) GO CREATE FUNCTION dbo.ufn_GetSubtree ( @mgrid AS int ) RETURNS @tree table ( empid int NOT NULL, mgrid int NULL, empname varchar(25) NOT NULL, salary money NOT NULL, lvl int NOT NULL, path varchar(900) NOT NULL ) AS BEGIN DECLARE @lvl AS int, @path AS varchar(900) SELECT @lvl = 0, @path = '.' INSERT INTO @tree SELECT empid, mgrid, empname, salary, @lvl, '.' + CAST(empid AS varchar(10)) + '.' FROM Employees WHERE empid = @mgrid WHILE @@ROWCOUNT > 0 BEGIN SET @lvl = @lvl + 1 INSERT INTO @tree SELECT E.empid, E.mgrid, E.empname, E.salary, @lvl, T.path + CAST(E.empid AS varchar(10)) + '.' FROM Employees AS E JOIN @tree AS T ON E.mgrid = T.empid AND T.lvl = @lvl - 1 END RETURN END GO SELECT empid, mgrid, empname, salary FROM ufn_GetSubtree(3) GO /* empid mgrid empname salary 2 1 Andrew 5000.0000 5 2 Steven 2500.0000 6 2 Michael 2500.0000 */ /* SELECT REPLICATE (' | ', lvl) + empname AS employee FROM ufn_GetSubtree(1) ORDER BY path */ /* employee ----------------- Nancy | Andrew | | Steven | | Michael | Janet | | Robert | | | David | | | | James | | | Ron | | | Dan | | Laura | | Ann | Margaret | | Ina */ "Christian Perthen" <abracadabara@dontreplytothisaddress.com> wrote in message news:uefIdZxtEHA.2596@TK2MSFTNGP10.phx.gbl... [quoted text, click to view] > 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 > >
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 [quoted text, click to view] "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 > > > > > >
Sorry, it's all in the cursor declaration, declare the cursor as
DECLARE myCur CURSOR LOCAL FORWARD_ONLY STATIC FOR "Christian Perthen" <abracadabara@dontreplytothisaddress.com> wrote in message news:ukcKJC5tEHA.2128@TK2MSFTNGP11.phx.gbl... [quoted text, click to view] > 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 > > > > > > > > > > > >
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... [quoted text, click to view] > 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 > > > > > > > > > > > >
Don't see what you're looking for? Try a search.
|
January 2003
March 2003
April 2003
May 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
April 2008
August 2008
| home · blog · groups | Questions? Comments? Contact the dn |