ALTER PROCEDURE [Common].[usp_Upload_ReturnOrganizationData]
@moduleID INT,
@EffectiveYear CHAR(4)
AS
SELECT distinct EffectiveFrom
,EffectiveTo
,OrgUnit
,LevelNo
,ShortDescription
,LongDescription
INTO #Temp
FROM
(select * from CBPFR.tblOrganization where @moduleID=1
UNION ALL SELECT * FROM ShopAccount.tblOrganization WHERE @moduleID=2
UNION ALL SELECT * FROM BMR.tblOrganization WHERE @moduleID=5
) Tab
WHERE Tab.EffectiveYear=@EffectiveYear
SELECT
EffectiveFrom
,EffectiveTo
,OrgLevel1
,OrgLevel2
,OrgLevel3
,OrgLevel4
,OrgLevel5
,OrgLevel6
,OrgLevel7
,OrgLevel8
,OrgLevel9
,OrgLevel10
,L1LongDescription=(SELECT TOP 1 LongDescription FROM #Temp T WHERE OrgLevel1=OrgUnit AND LevelNo=1 AND Org.EffectiveFrom=T.EffectiveFrom AND Org.EffectiveTo=T.EffectiveTo)
,L1ShortDescription=(SELECT TOP 1 ShortDescription FROM #Temp T WHERE OrgLevel1=OrgUnit AND LevelNo=1 AND Org.EffectiveFrom=T.EffectiveFrom AND Org.EffectiveTo=T.EffectiveTo)
,L2LongDescription=(SELECT TOP 1 LongDescription FROM #Temp T WHERE OrgLevel2=OrgUnit AND LevelNo=2 AND Org.EffectiveFrom=T.EffectiveFrom AND Org.EffectiveTo=T.EffectiveTo)
,L2ShortDescription =(SELECT TOP 1 ShortDescription FROM #Temp T WHERE OrgLevel2=OrgUnit AND LevelNo=2 AND Org.EffectiveFrom=T.EffectiveFrom AND Org.EffectiveTo=T.EffectiveTo)
,L3LongDescription =(SELECT TOP 1 LongDescription FROM #Temp T WHERE OrgLevel3=OrgUnit AND LevelNo=3 AND Org.EffectiveFrom=T.EffectiveFrom AND Org.EffectiveTo=T.EffectiveTo)
,L3ShortDescription=(SELECT TOP 1 ShortDescription FROM #Temp T WHERE OrgLevel3=OrgUnit AND LevelNo=3 AND Org.EffectiveFrom=T.EffectiveFrom AND Org.EffectiveTo=T.EffectiveTo)
,L4LongDescription=(SELECT TOP 1 LongDescription FROM #Temp T WHERE OrgLevel4=OrgUnit AND LevelNo=4 AND Org.EffectiveFrom=T.EffectiveFrom AND Org.EffectiveTo=T.EffectiveTo)
,L4ShortDescription=(SELECT TOP 1 ShortDescription FROM #Temp T WHERE OrgLevel4=OrgUnit AND LevelNo=4 AND Org.EffectiveFrom=T.EffectiveFrom AND Org.EffectiveTo=T.EffectiveTo)
,L5LongDescription=(SELECT TOP 1 LongDescription FROM #Temp T WHERE OrgLevel5=OrgUnit AND LevelNo=5 AND Org.EffectiveFrom=T.EffectiveFrom AND Org.EffectiveTo=T.EffectiveTo)
,L5ShortDescription =(SELECT TOP 1 ShortDescription FROM #Temp T WHERE OrgLevel5=OrgUnit AND LevelNo=5 AND Org.EffectiveFrom=T.EffectiveFrom AND Org.EffectiveTo=T.EffectiveTo)
,L6LongDescription =(SELECT TOP 1 LongDescription FROM #Temp T WHERE OrgLevel6=OrgUnit AND LevelNo=6 AND Org.EffectiveFrom=T.EffectiveFrom AND Org.EffectiveTo=T.EffectiveTo)
,L6ShortDescription =(SELECT TOP 1 ShortDescription FROM #Temp T WHERE OrgLevel6=OrgUnit AND LevelNo=6 AND Org.EffectiveFrom=T.EffectiveFrom AND Org.EffectiveTo=T.EffectiveTo)
,L7LongDescription =(SELECT TOP 1 LongDescription FROM #Temp T WHERE OrgLevel7=OrgUnit AND LevelNo=7 AND Org.EffectiveFrom=T.EffectiveFrom AND Org.EffectiveTo=T.EffectiveTo)
,L7ShortDescription =(SELECT TOP 1 ShortDescription FROM #Temp T WHERE OrgLevel7=OrgUnit AND LevelNo=7 AND Org.EffectiveFrom=T.EffectiveFrom AND Org.EffectiveTo=T.EffectiveTo)
,L8LongDescription =(SELECT TOP 1 LongDescription FROM #Temp T WHERE OrgLevel8=OrgUnit AND LevelNo=8 AND Org.EffectiveFrom=T.EffectiveFrom AND Org.EffectiveTo=T.EffectiveTo)
,L8ShortDescription =(SELECT TOP 1 ShortDescription FROM #Temp T WHERE OrgLevel8=OrgUnit AND LevelNo=8 AND Org.EffectiveFrom=T.EffectiveFrom AND Org.EffectiveTo=T.EffectiveTo)
,L9LongDescription =(SELECT TOP 1 LongDescription FROM #Temp T WHERE OrgLevel9=OrgUnit AND LevelNo=9 AND Org.EffectiveFrom=T.EffectiveFrom AND Org.EffectiveTo=T.EffectiveTo)
,L9ShortDescription =(SELECT TOP 1 ShortDescription FROM #Temp T WHERE OrgLevel9=OrgUnit AND LevelNo=9 AND Org.EffectiveFrom=T.EffectiveFrom AND Org.EffectiveTo=T.EffectiveTo)
,L10LongDescription =(SELECT TOP 1 LongDescription FROM #Temp T WHERE OrgLevel10=OrgUnit AND LevelNo=10 AND Org.EffectiveFrom=T.EffectiveFrom AND Org.EffectiveTo=T.EffectiveTo)
,L10ShortDescription=(SELECT TOP 1 ShortDescription FROM #Temp T WHERE OrgLevel10=OrgUnit AND LevelNo=10 AND Org.EffectiveFrom=T.EffectiveFrom AND Org.EffectiveTo=T.EffectiveTo)
FROM
(select * from CBPFR.tblOrganization where @moduleID=1
UNION ALL SELECT * FROM ShopAccount.tblOrganization WHERE @moduleID=2
UNION ALL SELECT * FROM BMR.tblOrganization WHERE @moduleID=5
) Org
WHERE Org.IsOriginalRecord=1
AND Org.EffectiveYear=@EffectiveYear
先判断where语句 ,然后在select