Paged Results in T-SQL

23. February 2010 19:08

Okay, so I wanted to return a paged result set from a Stored Procedure in Microsoft SQL Server in T-SQL. The results in question can be easily fed via a web service endpoint to a Silverlight, DHTML or other dynamic grid. My own use is to populate a jqGrid.

The features I need are to be able to return only the relevant results for the current page, as well as a count of the total rows available. I need to be able to input the current page number, an arbitrary page size, as well as dynamically sort on a given column. My real world use is a fairly complex set of joined tables, with several input parameters, so I am going to limit me queries to one table with the following format:

CREATE TABLE MyList (
    [id] UNIQUEIDENTIFIER PRIMARY KEY DEFAULT NEWID(),
    [created] DATETIME NOT NULL DEFAULT GETDATE(),
    [name] NVARCHAR(100) UNIQUE NOT NULL DEFAULT '',
    [description] NVARCHAR(250) UNIQUE NOT NULL DEFAULT ''
);

From here I'm going to want to do a stored procedure with the following signature:

CREATE PROCEDURE [Get_MyList_Paged]
    /*Paging Inputs*/
    @Page_Size INT = 100,
    @Page_Number INT = 1,
    @Sort_Column VARCHAR(100),  /* ('created','name','description') */
    @Sort_Direction VARCHAR(4), /* ('ASC','DESC') */
    
    /*Paging Total For Output*/
    @Row_Count BIGINT OUT,
    
    /*Result Filters*/
    @id UNIQUEIDENTIFIER,
    @created_filter DATETIME,   /* ('ne','g','ge','e','le','l') */
    @created_date DATETIME,
    @name NVARCHAR(100),
    @description NVARCHAR(500)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    /*========================================================================
    Declare local variables
    ========================================================================*/
    -- TODO: Insert local variables
    -- TODO: create a temporary space for paged result set
    
    /*========================================================================
    Normalize Paging Parameters
    ========================================================================*/
    -- TODO: Setup FirstRecord and LastRecord for results
    -- TODO: Ensure that sorting params are in the allowed fields
    
    /*========================================================================
    Normalize Filter Parameters
    ========================================================================*/
    -- TODO: make certain that the input parameters are valid entries
    
    /*========================================================================
    Prepare Results
    ========================================================================*/
    -- TODO: input the paged results into a temporary space
    
    /*========================================================================
    Return Results
    ========================================================================*/
    -- TODO: return the output params, and select the return results
END

The signature makes it apparent that I will want to page my result set, as well as do a dynamic sort for the results in addition to making some filtering options available. The next step is to get the first and last record you will be able to return.

    /*========================================================================
    Declare local variables
    ========================================================================*/
    DECLARE @FirstRecord int
    DECLARE @LastRecord int
    
    ...
    
    /*========================================================================
    Normalize Paging Parameters
    ========================================================================*/
    --Fix invalid input for Page Size
    SET @Page_Size = CASE 
        WHEN @Page_Size IS NULL THEN 100
        WHEN @Page_Size < 1 THEN 100
        ELSE @Page_Size
    END;

    --Fix invalid input for Page Number
    SET @Page_Number = CASE
        WHEN @Page_Number IS NULL THEN 1
        WHEN @Page_Number < 1 THEN 1
        ELSE @Page_Number
    END;

    --starting record to use.
    SET @FirstRecord = ((@Page_Number - 1) * @Page_Size) + 1

    --last record to use.
    SET @LastRecord = @FirstRecord + @Page_Size - 1

The code above will normalize for the paged results, and set an appropriate default. The @FirstRecord and @LastRecord are inclusive numbers to use for the current page's results.

Now that we have the paging parameters normalized, we should work on the sorting parameters. We want to make certain that our sort column and direction are valid...

    ...

    --ensure sort column is valid in the list
    SET @Sort_Column = CASE
        WHEN LOWER(@Sort_Column) IN ('created','name','description')
            THEN LOWER(@Sort_Column)
        ELSE
            'created' --default
    END
    
    --ensure sort direction is ASC or DESC
    SET @Sort_Direction = CASE
        WHEN LEFT(UPPER(COALESCE(@Sort_Direction, '')) + '    ', 4) = 'DESC' 
            THEN 'DESC' --explicit descending
        WHEN @Sort_Column = 'created' AND LEFT(UPPER(COALESCE(@Sort_Direction,'')) + '   ', 3) <> 'ASC' THEN
            'DESC' --default for created date
        ELSE 'ASC' --default otherwise
    END;

Here I've made certain that the @Sort_Column is set to an available sort option, otherwise it gets set to default. For the sort direction, I make sure to reduce the direction to allow for the use of the longer terms ASCENDING and DESCENDING, as well as making the default when the sort column is "created" to descending. I feel that coding defensively is important here.

Next up comes the input filters...

    /*========================================================================
    Normalize Filter Parameters
    ========================================================================*/
    --ensure that id is a valid non-default guid, or null
    SET @id = CASE @id
        WHEN '00000000-0000-0000-0000-000000000000' THEN null
        ELSE @id
    END
    
    --When an id is specified, only search on the id
    IF (@id IS NOT NULL)
    BEGIN
        --set all other input filters to null
        SET @created_filter = null
        SET @created_date = null
        SET @name = null
        SET @description = null
    END
    ELSE
    BEGIN
        --validate other input filters

        -- @created_date
        SET @created_date = DATEADD(dd, 0, DATEDIFF(dd, 0, @created_date))
        
        -- @created_filter
        SET @created_filter = CASE
            WHEN LOWER(@created_filter) in ('ne','g','ge','e','le','l') THEN
                LOWER(@created_filter)
            ELSE
                null
        END;
        
        -- created filtering needs a date and a filter
        IF (@created_filter IS NULL OR @created_date IS NULL)
        BEGIN
            SET @created_date = null
            SET @created_filter = null
        END;
        
        
        -- @name - if it's an empty string, make it null
        SET @name = CASE 
            WHEN COALESCE(LTRIM(RTRIM(@name)), '') = ''
                THEN null
            ELSE
                LTRIM(RTRIM(@name))
        END;
        
        
        -- @description - if it's an empty string, make it null
        SET @description = CASE 
            WHEN COALESCE(LTRIM(RTRIM(@description)), '') = ''
                THEN null
            ELSE
                LTRIM(RTRIM(@description))
        END;

    END

Okay, now all of our inputs have been normalized for better searching further down. It's worth noting, that I am normalizing the @created_date to the date portion only, if you are using UTC in your database, you'll want to avoid this, and stick with your external date-time localized to UTC for the stored procedure.

In order to prepare the results, we are first going to create a temporary table within the stored procedure in order to store our paged result set. I am going to use this temporary space in order to separate the individual row number, and row count from the returned results. You'll want to add this next section into the bottom of your local variable declaration.

    DECLARE @PagedResults AS TABLE (
        [id] UNIQUEIDENTIFIER,
        [created] DATETIME,
        [name] NVARCHAR(100),
        [description] NVARCHAR(500),
        [Row_Number] BIGINT,
        [Row_Count] BIGINT
    );

This temporary space will include each of the columns that we want to return as well as a Row_Number which is used for the results paging, as well as a Row_Count which is used for the output parameter. Most client-side grids will determin their own paging based on a total row count that gets returned in the remote call.

Next we will set up our results, first I'm going to show you a shell of how the selection will work.

    /*========================================================================
    Prepare Results
    ========================================================================*/
    WITH MyTempArea AS (
        SELECT TOP (@LastRecord)
            [id],
            [created],
            [name],
            [description],
            ROW_NUMBER() OVER (
                /*ORDER BY HERE*/
            ) AS [Row_Number],
            COUNT(*) OVER () AS [Row_Count]
        FROM
            MyList
        /*WHERE CLAUSE HERE */
    )
    INSERT INTO @PagedResults
    SELECT * FROM [MyTempArea] WHERE [Row_Number] >= @FirstRecord;

The template above is a general template in order to produce a paged result set. The WITH statement defines a given query that is used as part of the later select. The "TOP (@LastRecord)" will limit the inner query to the upper result limit. The ROW_NUMBER method gives each result a number, the sorting will be whatever sorting is done inside the OVER statement with the ROW_NUMBER method, and the COUNT(*) will give a total row count available from the inner query. The WHERE clause can be defined within the inner query. Finally the results, starting with @FirstRecord will be inserted into @PagedResults for further use.

Next we'll expand on handling the sorting dynamically.

    ....
            ROW_NUMBER() OVER (
                ORDER BY
                    CASE WHEN(@Sort_Direction = 'ASC') THEN CASE WHEN @Sort_Column='created'      THEN [created] END END ASC,
                    CASE WHEN(@Sort_Direction = 'ASC') THEN CASE WHEN @Sort_Column='name'         THEN [name] END END ASC,
                    CASE WHEN(@Sort_Direction = 'ASC') THEN CASE WHEN @Sort_Column='description'  THEN [description'] END END ASC,
                    CASE WHEN(@Sort_Direction = 'DESC') THEN CASE WHEN @Sort_Column='created'     THEN [created] END END DESC,
                    CASE WHEN(@Sort_Direction = 'DESC') THEN CASE WHEN @Sort_Column='name'        THEN [name] END END DESC,
                    CASE WHEN(@Sort_Direction = 'DESC') THEN CASE WHEN @Sort_Column='description' THEN [description'] END END DESC
            ) AS [Row_Number]
    ....

Again the reason this sorting happens within the ROW_NUMBER method's OVER, is calling the ROW_NUMBER will sort the results by ROW_NUMBER. The sorting itself may seem somewhat confusing, in essense for each column that may be part of a sort, you will want to have a CASE statement for that pair, if you need a multicolumn sort, then do the same case statement with the other column in the THEN portion. You can't include the ASC and DESC as part of thw CASE statement, but you can do the column names. It's necessary to do the nested WHEN clause in order to prevent correlation between the different column's types in SQL's query optimizer. Next to the paging, the dynamic handling of sorting is the most complicated piece of this puzzle.

Next comes the WHERE clause. This gets somewhat complicated, and you can adjust to suit your needs, I'm going to use a couple tricks for hanlding the default inputs.

    ...
        WHERE
            [id] = COALESCE(@id, [id])
            AND (
                @name is null
                OR    
                @name like '%' + COALESCE(@name, [name]) + '%'
            )
            AND (
                @description is null
                OR
                @description like '%' + COALESCE(@description, [description]) + '%'
            )
            AND (
                @created_filter is null
                OR ( @created_filter = 'ne' AND [created] < @created_date AND [created] >= (@created_date + 1) ) 
                OR ( @created_filter = 'g' AND [created] > (@created_date + 1) ) 
                OR ( @created_filter = 'ge' AND [created] >= @created_date ) 
                OR ( @created_filter = 'e' AND [created] >= @created_date AND [created] < (@created_date + 1) )
                OR ( @created_filter = 'le' AND [created] < (@created_date + 1) )
                OR ( @created_filter = 'l' AND [created] < @created_date )
            )
    ...

First, the expected @created_date filter should be the beginning of the day/date ie. the date part only, if you're using UTC for date/time storage within the database, you should avoid the normalizing of the date to the date portion in the filter normalization.

Last, but not least, you'll need to return your results...

    /*========================================================================
    Return Results
    ========================================================================*/
    -- @Row_Count output param
    SELECT @Row_Count = COALESCE(MAX(Row_Count), 0) FROM @PagedResults;

    -- Paged results set to return
    SELECT    
            [id]
            ,[created]
            ,[name]
            ,[description]
    FROM @PagedResults
    ORDER BY [Row_Number];
    ...

Here's the demo script for download: PagedResultDemo.sql (9.37 kb)

Comments are closed

Tracker1

Michael J. Ryan aka Tracker1

My name is Michael J. Ryan and I've been developing web based applications since the mid 90's.

I am an advanced Web UX developer with a near expert knowledge of JavaScript.