--注意蓝色字体的部分是未授权菜单的记录.在更新权限记录时可插入新记录(if PermissionID= =0).
ALTER Procedure GetMenusByRoleID
@RoleID int
AS
SELECT Menus.MenuName, Menus.TargetUrl, Menus.FatherID, Menus.MenuID,
Permission.PermissionValue, Permission.PermissionID
FROM Menus INNER JOIN
Permission ON Menus.MenuID = Permission.MenuID
WHERE (Permission.RoleID = @RoleID)
union
select menus.menuname,menus.targeturl,menus.fatherid,menus.menuid,0,0
from menus where menuid not in(select distinct permission.menuid from permission)
order by menus.menuid
GO