Iterations and array in T-Sql

Question

Let's say I have this class Location, which represents locations.

public class Location
{
  public int LocationID { get; set; }
  public int JuridictionID { get; set; }
  public int LocationName { get; set; }
}

Exemple: Silver Spring is located in Montgomery County, which is located in Maryland, Which is located in the USA. Thus, JuriditionID for Maryland would be the the LocationID for the USA, and so on.

public List<Location> SetHierarchyOfLocation(int JuridicitionID)
{
        var list = new List<Location>();
        if (JuridicitionID > 0)
        {
            while (JuridicitionID > 0)
            {
                var location = new Location();
                location = GetLocationByID(JuridicitionID);                    
                list.Add(location);
                JuridicitionID = location.JuridictionID;
            }
        }
        list.Reverse();
        return list;
    }

pubilc Location GetLocationByID(int LocationID)
{
  return db.Locations.FirstOrDefault(l => l.LocationID == LocationID);
}

This works perfectly. But, now I realize that it will result in a lot of single calls to the database.

But I don't know how to write a stored procedure that will replace this. Can someone help me???

Thanks.

Which SQL Server version? You may want to look at this question: http://stackoverflow.com/questions/202912/hierarchical-data-in-linq-options-and-performance — Kevin Stricker, Oct 17 '11 at 01:22
You probably want `jurisdiction` - note the **s**. Avoid spelling mistakes in your code. http://programmers.stackexchange.com/questions/30895/should-i-point-out-spelling-grammar-related-mistakes-in-someones-code — Kirk Broadhurst, Oct 17 '11 at 01:44

t-clausen.dk · Accepted Answer · 2011-10-21T21:43:08.183

1

I made a table like yours called [location]

create table location(locationid int, JuridicitionID int, placename varchar(30))

insert location values(1, 0, 'USA')
insert location values(2, 1, 'Maryland')
insert location values(3, 2, 'Silver Spring')

go

create procedure SetHierarchyOfLocation(@JuridicitionID int) 
as
begin
;with cte as
(
select placename, JuridicitionID
from location where JuridicitionID = @JuridicitionID
union all
select t.placename, t.JuridicitionID
from cte join location t on cte.JuridicitionID = t.locationid
)
select placename from cte
order by JuridicitionID
end

go
exec SetHierarchyOfLocation 2
go

edited Oct 21 '11 at 21:43

answered Oct 17 '11 at 08:04

t-clausen.dk

43,517
12
59
92

When I execute the SPROC with 3 as parameter, I get USA, Maryland, and Montgomery County 4 times. It looks like the answer is good, except that I'm getting everything 4 times. – Richard77 Oct 19 '11 at 00:04
@Richard77 I think you have some corrupt data – t-clausen.dk Oct 19 '11 at 06:18
That's possible. can u add more data yourself so that we can see how the above code behave??? – Richard77 Oct 19 '11 at 23:14
@Richard77 I see what you mean. It has been fixed. – t-clausen.dk Oct 21 '11 at 21:43
Thank you. I found your solution in a new book I just bought. APress, SQL SERVER 2008 TRANSACT-SQL RECIPES, Joseph SACK, p. 59 – Richard77 Nov 05 '11 at 21:05

Iterations and array in T-Sql

1 Answers1