468,272 Members | 2,136 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,272 developers. It's quick & easy.

Dynamic Build SQL in store procedure based on select

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

Jul 23 '05 #1
3 1375
On 4 Apr 2005 15:04:10 -0700, HL****@gmail.com 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)
Jul 23 '05 #2
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.public.sqlserver.programming. 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)
Jul 23 '05 #3
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.

Jul 23 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by Jaidev Paruchuri | last post: by
5 posts views Thread by C White | last post: by
7 posts views Thread by Michael C# | last post: by
1 post views Thread by Nathan Bloomfield | last post: by
2 posts views Thread by John Ninan | last post: by
1 post views Thread by Sachi | last post: by
reply views Thread by NPC403 | last post: by
reply views Thread by zattat | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.