Je5bVEGT

1USE [DB]
2GO
3
4SET ANSI_NULLS ON
5GO
6SET QUOTED_IDENTIFIER ON
7GO
8
9ALTER PROCEDURE [dbo].[storedProcedure] 
10@UserLogin varchar(20) = null 
11AS
12
13create table #xxf(RegionCode varchar(10), RegionName varchar(50))
14 select distinct RegionCode, Zone
15 into  #MasterAccess 
16 from  RegionZoneAccess 
17 where RegionCode <> 'ALL'  or Zone <> 'ALL'
18
19 insert into #xxf
20 select distinct d.regioncode, d.regioncode + ' - ' + d.regionname         regionname
21 from  FordNA.dbo.Dealers d, #MasterAccess m
22 where d.approveddate is not null and
23 d.termdate is null and d.regioncode <> 'A1' and
24 d.RegionCode collate SQL_Latin1_General_CP1_CI_AS = m.RegionCode collate    SQL_Latin1_General_CP1_CI_AS and
25 d.Zone collate SQL_Latin1_General_CP1_CI_AS = m.Zone collate    SQL_Latin1_General_CP1_CI_AS
26
27 insert into #xxf
28 select distinct 'A1-' + ltrim(rtrim(DistrictCode)), 'A1-' +  ltrim(rtrim(DistrictCode)) + ' - ' + ltrim(rtrim(DistrictName))
29  from SelectDealerDistricts
30   --where exists (select * from #MasterAccess where RegionCode like 'A1%')
31
32  if @UserLogin is not null
33      delete from #xxf
34      where not exists (select *
35                  from RegionZoneAccess r
36                  where r.UserLogin = @UserLogin and
37                        (r.RegionCode collate SQL_Latin1_General_CP1_CI_AS =   #xxf.RegionCode collate SQL_Latin1_General_CP1_CI_AS or r.RegionCode = 'ALL'))
38
39    select * from #xxf order by regioncode
40	
41select distinct d.RegionCode,(r.regioncode + ' - ' + s.DistrictName)    regionname from   FordNA.dbo.Dealers d 
42inner join RegionZoneAccess r  on r.RegionCode <> 'ALL'  or r.Zone <> 'ALL'      and d.approveddate is not null and
43d.termdate is null and d.regioncode <> 'A1' and
44 d.RegionCode collate SQL_Latin1_General_CP1_CI_AS = r.RegionCode collate   SQL_Latin1_General_CP1_CI_AS and
45 d.Zone collate SQL_Latin1_General_CP1_CI_AS = r.Zone collate     
46  SQL_Latin1_General_CP1_CI_AS inner join SelectDealerDistricts s on 
47   r.UserLogin = 'lliving6' and 
48  r.RegionCode 
49 collate SQL_Latin1_General_CP1_CI_AS = d.RegionCode collate SQL_Latin1_General_CP1_CI_AS or r.RegionCode = 'ALL'