drop procedure ap_DealTerritory_GetDescription
go
create procedure ap_DealTerritory_GetDescription
	@nDealID int,
	@cTerritoryIDs varchar(1000) output,
	@cTerritoryNames varchar(8000) output
as

set ansi_warnings off
set nocount on

create table #t(
	-- just to keep stuff in order
	i int identity(1,1) primary key clustered,
	nTerritoryID int not null,
	nChildTerritoryID int not null,
	cTerritoryIDList varchar(1000) null,
	cTerritory varchar(100) not null,
	cTerritoryList varchar(5000) null
)

insert into #t (nTerritoryID, nChildTerritoryID, cTerritory)

select	A.nTerritoryID,
	T.nTerritoryID,
	T.cTerritoryDescription
from
(
	select	T.nTerritoryID,
		T.cTerritoryDescription,
		T.nLeft,
		T.nRight
	from	DealTerritory DT
	join	Territory T
	on	DT.nTerritoryID = T.nTerritoryID
	join	Territory T_Parent
	on	T.nLeft between T_Parent.nLeft and T_Parent.nRight
	left join DealTerritory DT_Parent
	on	T_Parent.nTerritoryID = DT_Parent.nTerritoryID
	and	DT.nDealID = DT_Parent.nDealID
	and	DT_Parent.nTerritoryID <> T.nTerritoryID
	where	DT.nDealID = @nDealID
	group by T.nTerritoryID, T.cTerritoryDescription, T.nLeft, T.nRight
	having	sum(DT_Parent.nTerritoryID) is NULL
) A
join	Territory T
on	T.nLeft between A.nLeft and A.nRight
left join DealTerritory DT
on	DT.nDealID = @nDealID
and	T.nTerritoryID = DT.nTerritoryID
and	DT.nTerritoryID <> A.nTerritoryID
where	DT.nTerritoryID is NULL
order by T.nLeft


declare
    @list varchar(8000),
    @IDlist varchar(1000),
    @lasti int

select
    @list = '',
    @lasti = -1

--here is the meat of the work
update
    #t
set
    @list = cTerritoryList = case
                       when @lasti <> nTerritoryID then cTerritory + ' excluding '
                       else @list + cTerritory + ', '
                   end,
    @IDlist = cTerritoryIDList = case
			when @lasti <> nTerritoryID then convert(varchar, nChildTerritoryID)
                       else @IDlist + ',' + convert(varchar, nChildTerritoryID)
                   end,
   @lasti = nTerritoryID

select	@cTerritoryNames = isnull(@cTerritoryNames + '<br>', '') + case
		when right(#t.cTerritoryList, 2) = ', ' then
			substring(#t.cTerritoryList, 1, len(#t.cTerritoryList) - 1)
		else
			#t.cTerritory
		end,
	@cTerritoryIDs = isnull(@cTerritoryIDs + ',', '') + cTerritoryIDList
from
(
	select	max(cTerritoryList) as 'cTerritoryList'
	from	#t
	group by nTerritoryID
) A
join	#t
on	A.cTerritoryList = #t.cTerritoryList
order by #t.i

go