一个sql server递归查找的实例

with [tmpUsers] as (SELECT
        row_number() over (order by U.UserName) AS rowid,
        U.*,
        UP.PortalId,
        UP.Authorised,
        UP.RefreshRoles
       FROM dbo.UserPortals AS UP
         RIGHT OUTER JOIN dbo.UserRoles  UR
         INNER JOIN dbo.Roles R ON UR.RoleID = R.RoleID
         RIGHT OUTER JOIN dbo.Users AS U ON UR.UserID = U.UserID
        ON UP.UserId = U.UserID 
       WHERE ( UP.PortalId = @PortalID OR @PortalID IS Null )
        AND (UP.IsDeleted = 0 OR UP.IsDeleted Is NULL)
        AND (R.RoleId = @RoleId)
        AND (R.PortalId = @PortalID OR @PortalID IS Null )
        AND (U.IsSuperUser = @IsSuperUser)
  )
  select * from [tmpUsers]
   where rowid > @PageLowerBound and rowid < @PageUpperBound
   order by rowid