SQL Server recursive CTE and paging

Question

I have this table

 CREATE TABLE [dbo].[friend_blocked_list](
 [subdomain] [varchar](50) NOT NULL,
 [un] [nvarchar](50) NOT NULL,
 [friend] [nvarchar](50) NOT NULL,
 [is_blocked] [bit] NOT NULL,
 [approved] [bit] NOT NULL)

where i select data from it with below query. Select query combines users that added user as friend and users that have been added as friend by user

declare @un varchar(50), @subdomain varchar(50)

set @un='user2';
set @subdomain ='test.domain.com';

WITH FRIENDS as
(
  SELECT friend
  FROM friend_blocked_list
  WHERE un=@un and subdomain=@subdomain and approved=1 and is_blocked=0

  UNION ALL

  SELECT un as friend
  FROM friend_blocked_list
  WHERE friend=@un and subdomain=@subdomain and approved=1 and is_blocked=0
)
select friend from FRIENDS group by FRIENDS.friend order by FRIENDS.friend asc

It works fine with small amout of data but i want to be able to do a paging on the server side in order to reduce load. I am trying to combine it with my paging sp below

create PROCEDURE [dbo].[Paging]
    @subdomain varchar(50),
    @un varchar(50),
    @PageNumber int,
    @PageSize int
AS
BEGIN
   --paging
   DECLARE @FirstRow INT,@LastRow INT,@RowCount INT,@PageCount INT

   --find recordcount and pages
   SELECT @RowCount = COUNT(*), @PageCount = COUNT(*) / @PageSize 
   FROM friend_blocked_list 
   WHERE subdomain=@subdomain AND un=@un AND approved=1 AND is_blocked=0;

   --- calculate pages    
   IF @RowCount % @PageSize != 0 SET @PageCount = @PageCount + 1 
   IF @PageNumber < 1 SET @PageNumber = 1 
   IF @PageNumber > @PageCount SET @PageNumber = @PageCount 

   SELECT 
        CurrentPage = @PageNumber, 
        TotalPages = @PageCount, 
        TotalRows = @RowCount 

   -- mora calculation
   SELECT @FirstRow = ( @PageNumber - 1) * @PageSize + 1,
          @LastRow = (@PageNumber - 1) * @PageSize + @PageSize ;

   WITH MyTopics  AS
   (
      SELECT *, ROW_NUMBER() OVER (order by un asc) AS RowNumber
      FROM friend_blocked_list 
      WHERE subdomain=@subdomain AND un=@un AND approved=1 AND is_blocked=0
   )
   SELECT *
   FROM MyTopics
   WHERE RowNumber BETWEEN @FirstRow AND @LastRow
   ORDER BY RowNumber ASC;
end

But as always i am having trouble :). Main problem is the UNION ALL in my query. It prevents me using ROW_NUMBER() OVER.

Any ideas?

Answer 1

Apply the row number to the list you want to return:

WITH FRIENDS as
(
SELECT friend
  FROM friend_blocked_list
  WHERE un=@un and subdomain=@subdomain and approved=1 and is_blocked=0
UNION ALL
SELECT un as friend
  FROM friend_blocked_list
  WHERE friend=@un and subdomain=@subdomain and approved=1 and is_blocked=0 )
, recursive_friends as  (
select friend 
 , row_number() over (order by friend asc) as rn
from FRIENDS )
select friend 
  from recursive_friends
  where rn between @firstRow and @lastRow
  order by FRIENDS.friend asc;

Answer 2

Here's your procedure updated for paging:

CREATE PROCEDURE [dbo].[Paging]
  @subdomain varchar(50),
  @un varchar(50),
  @PageNumber int,
  @PageSize int
AS

  DECLARE @start_row int
  DECLARE @end_row int

  SET @end_row = @PageNumber * @PageSize
  SET @start_row = @end_row - (@PageSize - 1)

BEGIN

  WITH FRIENDS AS (
    SELECT t.friend
      FROM FRIEND_BLOCKED_LIST t
     WHERE t.un = @un 
       AND t.subdomain = @subdomain 
       AND t.approved = 1 
       AND t.is_blocked = 0
   UNION ALL
    SELECT t.un as friend
      FROM FRIEND_BLOCKED_LIST t
     WHERE t.friend = @un 
       AND t.subdomain = @subdomain 
       AND t.approved = 1 
       AND t.is_blocked = 0)
SELECT t.friend
  FROM (SELECT f.friend,
               ROW_NUMBER() OVER (ORDER BY f.friend) AS rownum
          FROM FRIENDS f
      GROUP BY f.friend) t
 WHERE t.rownum BETWEEN @start_row AND @end_row

END

It might be possible to change the query to use one CTE if you could provide more information on the FRIEND_BLOCKED_LIST table. The UNION of two queries with identical WHERE clauses while differentiating between two columns makes me wonder if it couldn't be written better. Could the FRIENDS CTE be rewritten as:

SELECT COALESCE(t.un, t.friend) as friend
  FROM FRIEND_BLOCKED_LIST t
 WHERE @un = COALESCE(t.un, t.friend)
   AND t.subdomain = @subdomain 
   AND t.approved = 1 
   AND t.is_blocked = 0