12/12/2008

SQL Server Side Paging With a Validated Dynamic Order By

So this is what it has come to anymore. Everyone is all about server side paging via SQL Server. As well they should be! It is so much faster and more efficient than having ADO or ADO.NET bring back a ton of records and then chop it to page it. However, there has always been some problems when trying to accomplish this task, especially using a SQL database that is pre 2005.

This task is easier to accomplish in SQL 2005 and 2008 using the ROW_NUMBER() function. The part that gets flaky is having a dynamic order by clause in your SQL statement. Unfortunately, the only way to accomplish this is to write some dynamic SQL. In doing so, It can be hard to tell if the order by parameter received by the stored procedure is a valid one for the table you are selecting from.

Solution

Enter the "IsValidOrderBy" user-defined function. This is a little function that will tell you if the column and order in the dynamic order by parameter is a valid one for the select statement you are running.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
CREATE FUNCTION [dbo].[udf_OrderByExists] 
(
@TableName NVARCHAR(50),
@OrderBy NVARCHAR(50)
)
RETURNS BIT
AS
BEGIN

DECLARE @Result BIT
SET @Result = 0

DECLARE @TableColumns TABLE
(
[ColumnNameAndSort] NVARCHAR(100) NOT NULL
)

INSERT INTO @TableColumns
SELECT [Name]
FROM syscolumns
WHERE ID = OBJECT_ID(@TableName)

INSERT INTO @TableColumns
SELECT [Name] + ' ASC'
FROM syscolumns
WHERE ID = OBJECT_ID(@TableName)

INSERT INTO @TableColumns
SELECT [Name] + ' DESC'
FROM syscolumns
WHERE ID = OBJECT_ID(@TableName)

IF EXISTS(SELECT [ColumnNameAndSort] FROM
@TableColumns WHERE [ColumnNameAndSort] = @OrderBy)
SET @Result = 1

RETURN @Result

END

Here you can see that we are taking 2 inputs. The first one being the table name you are selecting from, and the second being the order by clause received by the stored procedure. The function will then return a bit telling you if the column and order was found for the table you are selecting from.

Example

A simple example of using this user defined function would be selecting from a table of products. In that case, your stored procedure could look like so

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
CREATE PROCEDURE [dbo].[usp_GetProductsPaged]
@SortExpression NVARCHAR(50),
@PageIndex INT,
@PageSize INT
AS

-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

IF ((SELECT [dbo].[udf_OrderByExists]('dbo.Products', @SortExpression)) = 0)
SET @SortExpression = 'Name'

DECLARE @sql AS NVARCHAR(MAX),
@ParamDefinition AS NVARCHAR(MAX),
@StartRowIndex INT,
@RecordCount INT

SELECT @RecordCount = COUNT([ProductID]) FROM [Products]

IF @PageIndex = 0
SET @PageIndex = 1
IF @PageSize = 0
SET @PageSize = @RecordCount
SET @StartRowIndex = ((@PageIndex * @PageSize) - @PageSize) + 1
SET @ParamDefinition = N'@paramStartRowIndex INT,
@paramPageSize INT'

SET @sql = N'SELECT
[ProductID],
[Name],
[Description],
[Price]
FROM (SELECT
[ProductID],
[Name],
[Description],
[Price],
ROW_NUMBER() OVER(ORDER BY ' + @SortExpression + ') AS [RowNumber]
FROM [Products]) AS [Prods]
WHERE [RowNumber] BETWEEN @paramStartRowIndex
AND (@paramStartRowIndex + @paramPageSize) - 1'

-- For testing
--PRINT @sql
--PRINT @StartRowIndex

EXEC sp_executesql @sql,
@ParamDefinition,
@paramStartRowIndex = @StartRowIndex,
@paramPageSize = @PageSize

SELECT @RecordCount AS [RecordCount]

As you can see, by calling **udf_OrderByExists **and passing in the parameters, if the order by does not fit the table, we then change it to be something known and valid.

Conclusion

With a simple and portable user defined function, we can ensure that the order by clauses going into our paging stored procedures are validated thus keeping integrity. It isn’t fun having to write and maintain dynamic SQL in stored procedures, but it can be done and also made a little bit safer. One last tip: Always use the sp_executesql, as this will tell the SQL server that the execution plan should be cached for re-use.

Hope this helps!

View Comments