CODEWITHSUNDEEP
t-sql
Richard77
Richard77

Reputation: 21641

Iterations and array in T-Sql

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.

Upvotes: 0

Views: 218

Answers (1)

t-clausen.dk
t-clausen.dk

Reputation: 44326

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

Upvotes: 1

Related Questions