ifs,thens and create temp tables question

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)

ifs,thens and create temp tables question

Author

Topic

Johnhamman
Starting Member

37 Posts

Posted - 2002-07-14 : 22:50:02

Hello All,
2 Questions
I have 3 tables which creates a heiarchy of Catagories and products. This is done by following the SQLTeam.com Article 'More Trees & Hierarchies in SQL'. Now on some categories, there are lots of subcategories and so those calls to the database dont need to involve the Products table. But on other times the call is too a catagory who is directly a parent of products and I need to pull the products.
1)My question is how do i do that. Do i use a if else statement? I have added the field 'haveprods' to the cat table which tells me if that category has products directly under it. If its as simple as an if else statement How should i tell the AsP or asp.net page that there are products coming back to it or no products coming back to it, or do i even need to tell the page that?
2.) How do i pull back only a certain number of items? so that the page only shows so many items but it doesnt depend on how many categories it shows. Like lets say i only wanted to show 10 items per page and catA had 7 items and Catb had 10 items but On the page i only show the 7 items of catA and 3 items of catB. This is done for pageing abilites.


Cat_nodes
-----------
node int primkey
parentnode int
CatID      int
Depth      tinyint
Lineage    varchar

Cat_tbl
-----------
cID int primkey
Name   varchar
haveprods   bit

Prod_tbl
-----------
pID  int primkey
Name   varchar
ParentCat int

My Select Stored Proc that i currently have.

SP_Select
(@parentCat int)

SELECT     T.Node, SPACE(T.Depth * 2) + C.Name AS Name, T.CatID, T.Lineage + LTRIM(STR(T.Node, 6, 0)) AS leneage, T.Depth, T.ParentNode
FROM         V020U18NUC_akolade.Cat_tbl C INNER JOIN
                      V020U18NUC_akolade.Cat_nodes T ON C.cID = T.CatID
WHERE     (T.Lineage + LTRIM(STR(T.Node, 6, 0)) LIKE
                          (SELECT     Lineage + LTRIM(STR(Node, 6, 0))
                            FROM          V020U18NUC_akolade.Cat_nodes
                            WHERE      CatID = @parentCat) + '%')
ORDER BY T.Lineage + LTRIM(STR(T.Node, 6, 0))

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources