Author: Steven Neiland
Published:

Warning: This blog entry was written two or more years ago. Therefore, it may contain broken links, out-dated or misleading content, or information that is just plain wrong. Please read on with caution.

Getting The Desired Range Of Rows

Finally once we have numbered the rows we simply select out the ones that match our desired range. To do this we wrap the previous SQL in another SELECT and filter by the startRow and endRow ranges.

-- Return the records in the specified range
SELECT
      x2.*
      ,@maxRowNumber as maxRowNumber
FROM (
       -- Apply row numbering (this is also where sorting takes place)
      SELECT
            x1.*
            ,ROW_NUMBER() OVER(
                  ORDER BY
                  -- START UGLY HACK --
                  --For each column we would like sortable create two case statements (one for ASC and one for DESC)
                  CASE WHEN lower(@orderCol) = 'userid' AND lower(@orderDir) = 'asc' THEN userId END ASC
                  ,CASE WHEN lower(@orderCol) = 'userid' AND lower(@orderDir) = 'desc' THEN userId END DESC
                  ,CASE WHEN lower(@orderCol) = 'username' AND lower(@orderDir) = 'asc' THEN username END ASC
                  ,CASE WHEN lower(@orderCol) = 'username' AND lower(@orderDir) = 'desc' THEN username END DESC
                  -- END UGLY HACK --
            ) AS rowNumber
      FROM (      
            -- Get all records that match the filter criteria
            SELECT
                  userid
                  ,username
            FROM dbo.users
            WHERE
            --put filtering conditions (excluding row number limits) here
            (@nameLike IS NULL OR username LIKE '%'+ @nameLike + '%')
      ) AS x1
) AS x2
WHERE
      rowNumber BETWEEN @startRow AND @endRow

You will note that we also included the value of the @maxRowNumber variable to the records we return at this stage. The first time the stored procedure is called, the default row number range will be passed to get some results to display on the first page. However to work out the appropriate paging mechanism the application will need know how many subsequent records exist.

The Final Code

Putting everything together we end up with the following code for our stored procedure.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Steven Neiland
-- Description:      Returns a paged of user records
-- =============================================
ALTER PROCEDURE dbo.proc_getUsersPaged
      @orderCol varchar(50),
      @orderDir varchar(4),
      @startRow int,
      @endRow int,
      @nameLike varchar(50) = null
AS

BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;
      
      -- Declare variables
      DECLARE @maxRowNum INT

      -- Get the total number of records matching the filter criteria
      SELECT @maxRowNum = COUNT(*)
      FROM dbo.users
      WHERE
      --put filtering conditions (excluding row number limits) here
      (@nameLike IS NULL OR @nameLike LIKE '%'+ @nameLike + '%')
      
      -- Return the records in the specified range
      SELECT
            x2.*
            ,@maxRowNumber as maxRowNumber
      FROM (
             -- Apply row numbering (this is also where sorting takes place)
            SELECT
                  x1.*
                  ,ROW_NUMBER() OVER(
                        ORDER BY
                        -- START UGLY HACK --
                        --For each column we would like sortable create two case statements (one for ASC and one for DESC)
                        CASE WHEN lower(@orderCol) = 'userid' AND lower(@orderDir) = 'asc' THEN userId END ASC
                        ,CASE WHEN lower(@orderCol) = 'userid' AND lower(@orderDir) = 'desc' THEN userId END DESC
                        ,CASE WHEN lower(@orderCol) = 'username' AND lower(@orderDir) = 'asc' THEN username END ASC
                        ,CASE WHEN lower(@orderCol) = 'username' AND lower(@orderDir) = 'desc' THEN username END DESC
                        -- END UGLY HACK --
                  ) AS rowNumber
            FROM (      
                  -- Get all records that match the filter criteria
                  SELECT
                        userid
                        ,username
                  FROM dbo.users
                  WHERE
                  --put filtering conditions (excluding row number limits) here
                  (@nameLike IS NULL OR username LIKE '%'+ @nameLike + '%')
            ) AS x1
      ) AS x2
      WHERE
            rowNumber BETWEEN @startRow AND @endRow
END
GO
1 2 3

Related Blog Postings

What Do You Think?

Reader Comments

Post a Comment

Comment Etiquette:

  • Please keep comments on-topic.
  • Please do not post unrelated questions or large chunks of code.
  • Please do not engage in flaming/abusive behaviour.
  • Comments that contain or appear to be advertisments, will not be published.
  • Comments that appear to be created for the purpose of linkbuilding to commercial sites will be removed.

We are all adults here so play nice.

*
*



Archives Blog Listing

Tag Listing

Learn CF In A Week

Treehouse

 
Fork me on GitHub