exec('
Select L1.userCode,emp.personFNameEng,emp.personLNameEng,L1.applicationCode,autApp.applicationname, L1.menucode,L1.menuname as Level1 ,L2.menuname as Level2,L3.menuname as Level3 from (SELECT dbo.AutUserMenu.userCode,dbo.AutMenu.applicationCode, dbo.AutMenu.menuLevel, dbo.AutMenu.menuCode, dbo.AutMenu.menuName, dbo.AutMenu.parentMenuCode
FROM dbo.AutUserMenu RIGHT OUTER JOIN
dbo.AutMenu ON dbo.AutUserMenu.menuCode = dbo.AutMenu.menuCode
WHERE (dbo.AutMenu.menuLevel = 0) AND dbo.AutUserMenu.usercode in ('+@usercode+') AND AutMenu.applicationCode in (select AutApplication.applicationCode from AutUserApplication RIGHT OUTER JOIN AutApplication
on AutUserApplication.applicationCode = AutApplication.applicationCode
where usercode in ('+@usercode+')) )as L1
Left join (SELECT dbo.AutUserMenu.userCode,dbo.AutMenu.applicationCode, dbo.AutMenu.menuLevel,dbo.AutMenu.menuCode, dbo.AutMenu.menuName, dbo.AutMenu.parentMenuCode
FROM dbo.AutUserMenu RIGHT OUTER JOIN
dbo.AutMenu ON dbo.AutUserMenu.menuCode = dbo.AutMenu.menuCode
WHERE (dbo.AutMenu.menuLevel <> 0) AND dbo.AutMenu.parentMenuCode in (SELECT distinct dbo.AutMenu.menuCode
FROM dbo.AutUserMenu RIGHT OUTER JOIN
dbo.AutMenu ON dbo.AutUserMenu.menuCode = dbo.AutMenu.menuCode
WHERE (dbo.AutMenu.menuLevel = 0) AND UserCode in ('+@usercode+') AND AutMenu.applicationCode in (select AutApplication.applicationCode from AutUserApplication RIGHT OUTER JOIN AutApplication
on AutUserApplication.applicationCode = AutApplication.applicationCode
where usercode in ('+@usercode+'))) AND dbo.AutUserMenu.UserCode in ('+@usercode+'))as L2
ON L1.menucode = L2.parentmenucode and L1.usercode = L2.usercode and L1.applicationcode = L2.applicationcode
Left join (SELECT dbo.AutUserMenu.userCode,dbo.AutMenu.applicationCode, dbo.AutMenu.menuLevel,dbo.AutMenu.menuCode, dbo.AutMenu.menuName, dbo.AutMenu.parentMenuCode
FROM dbo.AutUserMenu RIGHT OUTER JOIN
dbo.AutMenu ON dbo.AutUserMenu.menuCode = dbo.AutMenu.menuCode
WHERE (dbo.AutMenu.menuLevel <> 0) AND dbo.AutMenu.parentMenuCode in (SELECT dbo.AutMenu.menuCode
FROM dbo.AutUserMenu RIGHT OUTER JOIN
dbo.AutMenu ON dbo.AutUserMenu.menuCode = dbo.AutMenu.menuCode
WHERE (dbo.AutMenu.menuLevel <> 0) AND dbo.AutMenu.parentMenuCode in (SELECT dbo.AutMenu.menuCode
FROM dbo.AutUserMenu RIGHT OUTER JOIN
dbo.AutMenu ON dbo.AutUserMenu.menuCode = dbo.AutMenu.menuCode
WHERE (dbo.AutMenu.menuLevel = 0) AND UserCode in ('+@usercode+') AND AutMenu.applicationCode in (select AutApplication.applicationCode from AutUserApplication RIGHT OUTER JOIN AutApplication
on AutUserApplication.applicationCode = AutApplication.applicationCode
where usercode in ('+@usercode+'))) AND dbo.AutUserMenu.UserCode in ('+@usercode+')) AND dbo.AutUserMenu.UserCode in ('+@usercode+') )as L3
ON L2.menucode = L3.parentmenucode and L2.usercode = L3.usercode and L2.applicationcode = L3.applicationcode
INNER JOIN dbo.AutApplication as autApp ON L1.applicationcode = autApp.applicationcode
INNER JOIN dbo.EmpHrm_Tmp as emp ON L1.userCode = emp.EmpCode
Order By L1.userCode,autApp.applicationName
Load balance : Server 05
