根据角色获得权限记录的存储过程

--注意蓝色字体的部分是未授权菜单的记录.在更新权限记录时可插入新记录(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