通过where 作为swith的条件

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