Dynamic Build SQL in store procedure based on select

[HL5138]

I have a department table like this:
DeptID Department ParentID, Lineage
1 HR NULL (
2 Temp1 1 (1,
3 Temp2 2 (1,2
4 PC NULL (

I have a deptmember table like this:
DeptID MemberID IsManager
1 1 Y
4 1 Y

I need to query table to get all department belong to MemberID 1 with
all children departments.

My thought is:
1. Do Select * from deptmember where MemberID=1 and IsManager=Y
2. Loop thru this table to build SQL
Where Lineage like '%1' OR Lineage like '%4'
3. Select * from department using where statement from step 2.

How do you loop thru results from step1, Do I need to use a cursor?

Thanks,

HL

[Hugo Kornelis]

On 4 Apr 2005 15:04:10 -0700, HL****@gmail.co m wrote:

I have a department table like this:
DeptID Department ParentID, Lineage
1 HR NULL (
2 Temp1 1 (1,
3 Temp2 2 (1,2
4 PC NULL (

I have a deptmember table like this:
DeptID MemberID IsManager
1 1 Y
4 1 Y

I need to query table to get all department belong to MemberID 1 with
all children departments.

My thought is:
1. Do Select * from deptmember where MemberID=1 and IsManager=Y
2. Loop thru this table to build SQL
Where Lineage like '%1' OR Lineage like '%4'
3. Select * from department using where statement from step 2.

How do you loop thru results from step1, Do I need to use a cursor?

Thanks,

HL

Hi HL,

You should not loop at all. Instead, do it all in one set-based
operation:

SELECT d.DeptID, d.Department, -- better not to use
d.ParentID, d.Lineage -- SELECT * in prod code!!
FROM DeptMember AS dm
INNER JOIN Department AS d
ON d.Lineage LIKE '(' + STR(dm.DeptID) + ',%'
OR d.Lineage LIKE '%,' + STR(dm.DeptID) + ',%'
WHERE dm.MemberID = 1
AND dm.IsManeger = 'Y'

Note that I also changed the LIKE statement a bit. Maybe you need to
modify it further to accomodate your needs. Just using LIKE '%1' as you
posted would match no rows at all; LIKE '%1%' would work, but it would
match rows with Lineage '(13,7,5' as well (due to the 1 in 13).

Final note: you might wish to google this group for "nested sets model",
to see a different approach to storing hierarchies in an RDBMS that many
people prefer.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[Hugo Kornelis]

On Tue, 05 Apr 2005 10:00:37 +0200, Hugo Kornelis wrote:

Final note: you might wish to google this group for "nested sets model",
to see a different approach to storing hierarchies in an RDBMS that many
people prefer.

And if that fails, try googling for the same search phrase in the group
microsoft.publi c.sqlserver.pro gramming. I'm not sure if it's been
discussed here, but I do know it has been in that group - numerous
times.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[--CELKO--]

1) Look at the nested sets model for hierarchies or buy my book TREES
& HIERARCHIES IN SQL.

2) Do not use dynamic SQL, cursors or other procedural code in SQL
unless you ABSOLUTELY have to do so.