problem with sql syntax - 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 2005 Forums

Transact-SQL (2005)

problem with sql syntax

Author

Topic

moshe1001
Starting Member

1 Post

Posted - 2008-02-16 : 08:17:10

hi here is my interpellation in sql

and yes im looping and waiting in between each iteration -- to get results back over .

i have a Category table that have 3 columns :

table structure :

CategoryID CategoryName CategoryPID

1 Electricity -1 // all the (-1) are the fathers

2 For Garden -1

3 Computer -1

4 lawn-mower 2 // the son of "For Garden"

5 Memory Card 3 // the son of "Computer"

6 Lamp 1 // the son of "Electricity"

i want to make a menu in my website with this structure :

Electricity

Lamp

Microwave

vacuum cleaner

For Garden

Lawn-mower

Hammock

Computer

Memory Card

Mother Board

Screen

etc....

interpellation in sql:

ALTER procedure [dbo].[Category_Select_XML]

AS

DECLARE fileCsr CURSOR READ_ONLY FOR

select CategoryID,CategoryName,CategoryPID from dbo.Categories where CategoryPID=-1

DECLARE @CategoryID INT

DECLARE @CategoryName NVARCHAR(50)

DECLARE @CategoryPID INT

OPEN fileCsr

FETCH NEXT FROM fileCsr INTO @CategoryID,@CategoryName,@CategoryPID

WHILE @@FETCH_STATUS = 0

BEGIN

select 1 as tag,

null as parent,

null as [Category!1],

null as [CategoryP!2!ID],

null as [CategoryP!2!Name],

null as [CategoryChild!3!ID],

null as [CategoryChild!3!Name]

union all

select 2 as tag,

1 as parent,

null,

@CategoryID,

@CategoryName,

null,

null

union all

select 3 as tag,

2 as parent,

null,

null,

null,

CategoryID,

CategoryName

from dbo.Categories

where CategoryPID=@CategoryID

for xml explicit

FETCH NEXT FROM fileCsr INTO @CategoryID,@CategoryName,@CategoryPID

END

close fileCsr

DEALLOCATE fileCsr

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-16 : 12:08:42

You can use the recursive CTE approach if you are having expanding type of hierarchy.

;
With CategoryCTE (CategoryID,CategoryName) AS

(
SELECT CategoryID,CategoryName
FROM Category
WHERE CategoryPID=-1

UNION ALL

SELECT c.CategoryID,c.CategoryName
FROM Category c
INNER JOIN CategoryCTE cc
ON cc.CategoryID=c.CategoryPID

)

SELECT * FROM CategoryCTE for xml explicit

this should provide with required xml for front end to create menu

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources