What is the best way to loop through sql (SQL or Asp.net C#)?

Question

I am creating a list to send an e-mail to. The individual who is logged in has a field in the database of who they report to (unless there is an error or they report to no one).

So for instance if I am logged in and clicked the submit button in SQL it would say I report to 'John Doe'

I then need to grab who 'John Doe' reports to and add that to the list. I need to keep climbing the list until we reach the top of the company (the GID will be blank or null).

Using me as an example, I report to 'John Doe' who reports to 'Tom Doe'. Tom reports to no-one his usrReportsTo field is like this '00000000-0000-0000-0000-000000000000'.

If the usrReportsTo field is "" or NULL or '00000000-0000-0000-0000-000000000000' the loop should terminate.

Here is the sql I used.

What is the cleanest/neatest/most effecient way to perform this loop and is it better to do it in SQL or ASP.net C#?

select usrReportsTo from dbo.CVT_Users_usr, usrEmailAddress
WHERE RTRIM(usrFirstName) + ' ' + RTRIM(usrLastName) = 'James Wilson' 
-- Returns 'James Wilson' + email

SELECT RTRIM(usrFirstName) + ' ' + RTRIM(usrLastName) as Name, usrEmailAddress, usrReportsTo from dbo.CVT_Users_usr
WHERE usrGID = '38922F83-4B6E-499E-BF4F-EF0B094F47F7'
-- Returns 'John Doe' + email + reportsTo

SELECT RTRIM(usrFirstName) + ' ' + RTRIM(usrLastName) as Name, usrEmailAddress, usrReportsTo from dbo.CVT_Users_usr
WHERE usrGID = 'FB8F4939-3956-4834-9D89-D72AFB8BF3E0'
-- Returns 'Tom Doe' + email + reportsTo

Edit #3

Working copy of SQL just doesn't return 100% true data.

with cte AS ( select usrGID, RTRIM(usrFirstName) + ' ' + RTRIM(usrLastName) as Name, usrEmailAddress, usrReportsTo from dbo.CVT_Users_usr union all select u.usrGID, RTRIM(u.usrFirstName) + ' ' + RTRIM(u.usrLastName), cte.usrEmailAddress, cte.usrReportsTo from dbo.CVT_Users_usr u inner join cte on u.usrReportsTo = cte.usrGID ) select * from cte where Name = 'James Wilson'

-- Returns

usrGID Name usrEmailAddress usrReportsTo E1DAFC11-BE35-4730-9961-68EEF8D85DE4 James Wilson 38922F83-4B6E-499E-BF4F-EF0B094F47F7 E1DAFC11-BE35-4730-9961-68EEF8D85DE4 James Wilson [email protected] FB8F4939-3956-4834-9D89-D72AFB8BF3E0 E1DAFC11-BE35-4730-9961-68EEF8D85DE4 James Wilson [email protected] 00000000-0000-0000-0000-000000000000

Shouldn't the usrGID and name match the same way usrEmailAddress and usrReportsTo does? I tried chaanging the sql to be cte.USRGID and cte.Name but it gave the max recursion error.

Any ideas?

What database backend do you use, that makes a difference in this kind of code. — HLGEM
– HLGEM, Commented Aug 20, 2012 at 21:46

podiluska · Accepted Answer · 2012-08-20 22:11:08Z

6

Using a common table expression you can generate the complete result set in one SQL statement (via a recursive join), thus avoiding any looping at all.

A basic example with the key fields

create table #CVT_Users_usr (usrGid uniqueidentifier, usrEmailAddress varchar(50), usrFirstName varchar(20), usrLastName varchar(20), usrReportsTo uniqueidentifier)

insert #CVT_Users_usr values    
('38922F83-4B6E-499E-BF4F-EF0B094F47F7' , '[email protected]','james','wilson', 'E1DAFC11-BE35-4730-9961-68EEF8D85DE4'),
('E1DAFC11-BE35-4730-9961-68EEF8D85DE4', '[email protected]','john','doe', 'FB8F4939-3956-4834-9D89-D72AFB8BF3E0'),
('FB8F4939-3956-4834-9D89-D72AFB8BF3E0', '[email protected]','tom','doe' ,'00000000-0000-0000-0000-000000000000'),
('FE6899A5-63BA-42B7-A70E-011711A5FAC6', '[email protected]','ken', 'kenneths', 'FB8F4939-3956-4834-9D89-D72AFB8BF3E0')

declare @id uniqueidentifier
select @id = usrGid from #CVT_Users_usr where usrFirstName='james' and usrLastName='wilson'

;with cte as
(
select usrGid, usrEmailAddress, usrFirstName, usrLastName, usrReportsTo from #CVT_Users_usr
union all
select u.usrGid,  cte.usrEmailAddress, cte.usrFirstName, cte.usrLastName, cte.usrReportsTo 
from #CVT_Users_usr u
    inner join cte on u.usrReportsTo= cte.usrGid
)
    select usrFirstName + ' ' + usrLastName, usrEmailAddress  from cte
    where usrGid=@id

drop table #CVT_Users_usr

edited Aug 20, 2012 at 22:11

answered Aug 20, 2012 at 21:33

podiluska

51.6k7 gold badges102 silver badges105 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

James Wilson Over a year ago

Hrm, I'm going to add a edit to the post. The SQL wont run keep saying the multi part identifier cannot be found. @podiluska

HLGEM Over a year ago

@JamesWilson, his code does work (I ran it on my server), so it must be something in how you implemented it. Can you edit your question to show what code you ran to get that error? Also, are you by chance in compatibility mode for SQL Server 2000?

podiluska Over a year ago

@JamesWilson I've revised it adding the first/lastname fields.

James Wilson Over a year ago

I was able to get a working copy going, can post the new code if needed. But only some of the data returned is what it should be. And when I change u.USRGID to cte.USERGID it gives me the max recursion error. Going to delete older stuff from OP and add new SQL im using. @podiluska

podiluska Over a year ago

It's because you're doing the WHERE clause on the name - you're asking it to return a bunch of James Wilsons - filter on the ID and it works - reedited to use your column names

|

Collectives™ on Stack Overflow

What is the best way to loop through sql (SQL or Asp.net C#)?

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related