Evaluate My Stored Procedure For Paging, Filtering, And Sorting

This is for SQL Server 2000. The purpose of the procedure is to return a subset of a filtered and sorted result set. The subset, filter criteria, and sort column and sort direction can be set dynamically. It uses the rowcount technique for paging.

This would be used to drive an ASP.NET gridview which supports filtering of the data, paging, and sorting. Please let me know what improvements I can make or if you have an idea for a better solution. (I didn't put this in a vBulletin code block because personally I find two sets of scroll bars annoying, but I can if people think it's better).

CREATE PROCEDURE dbo.Books_GetFilteredSortedSubset
(
-- paging
@startRowIndex INT = 1,
@maximumRows INT = 999999,

-- sorting
@sortColumn NVARCHAR(30) = 'title_id',
@sortDirection NVARCHAR(4) = 'ASC',

-- filtering
@title VARCHAR(100) = NULL,
@type VARCHAR(30) = NULL,
@price MONEY = NULL
)
AS
BEGIN

DECLARE @sql NVARCHAR(4000)
DECLARE @parameters NVARCHAR(4000)
DECLARE @tableSource NVARCHAR(4000)
DECLARE @orderByExpression NVARCHAR(4000)
DECLARE @searchCondition NVARCHAR(4000)
DECLARE @uniqueKey NVARCHAR(30)


-- set the unique key used to ensure the rows are sorted deterministically
SET @uniqueKey = 'title_id'



-- build the FROM table source used throughout this procedure
SET @tableSource = 'titles t
inner join publishers p on t.pub_id = p.pub_id'




-- build the WHERE search condition used to control filtering throughout this procedure

SET @searchCondition = '(1 = 1)'

IF @title IS NOT NULL
SET @searchCondition = @searchCondition + ' AND (title LIKE ''%' + @title + '%'')'
IF @type IS NOT NULL
SET @searchCondition = @searchCondition + ' AND (type LIKE ''' + @type + '%'')'
IF @price IS NOT NULL
SET @searchCondition = @searchCondition + ' AND (price = ' + CAST(@price AS NVARCHAR) + ')'




-- build the ORDER BY expression used to control the sorting throughout this procedure

SET @orderByExpression = @sortColumn + ' ' + @sortDirection

-- add uniqeKey to ORDER BY statement to ensure consistent ordering of results when @sortColumn is not unique
IF @sortColumn <> @uniqueKey
SET @orderByExpression = @orderByExpression + ', ' + @uniqueKey + ' ' + @sortDirection





-- Get the column value at the position specified by @startRowIndex when the results are sorted in the desired sort order

SET @sql = 'SET ROWCOUNT @rowcount; SELECT @start_row = ' + @sortColumn + ', @start_row_id = ' + @uniqueKey +
' FROM ' + @tableSource +
' WHERE ' + @searchCondition + ' ORDER BY ' + @orderByExpression

PRINT @sql

SET @parameters = '@rowcount INT, @start_row sql_variant OUTPUT, @start_row_id sql_variant OUTPUT'

DECLARE @start_row sql_variant
DECLARE @start_row_id sql_variant

EXEC sp_executesql @sql, @parameters, @rowcount = @startRowIndex, @start_row = @start_row OUTPUT, @start_row_id = @start_row_id OUTPUT



-- Get the filtered subset of results

-- add sql to filter the results based on criteria passed in as parameters
SET @sql = 'SET ROWCOUNT @rowcount; ' +
'SELECT
t.title_id,
t.title,
t.price,
t.type,
p.pub_name,
p.city,
p.state,
p.country
FROM ' + @tableSource +
' WHERE (' + @searchCondition + ') AND '

-- add sql to control the starting row
IF @sortDirection = 'ASC'
SET @sql = @sql + '( (' + @sortColumn + ' > @start_row) OR (' +
@sortColumn + ' = @start_row AND ' + @uniqueKey + ' >= @start_row_id) )'
ELSE
SET @sql = @sql + '( (' + @sortColumn + ' < @start_row) OR (' +
@sortColumn + ' = @start_row AND ' + @uniqueKey + ' <= @start_row_id) )'

-- add sql to control the ordering of everything
SET @sql = @sql + ' ORDER BY ' + @orderByExpression

PRINT @sql

SET @parameters = '@rowcount INT, @start_row sql_variant, @start_row_id sql_variant'

EXEC sp_executesql @sql, @parameters, @rowcount = @maximumRows, @start_row = @start_row, @start_row_id = @start_row_id


-- Reset the rowcount for others
SET ROWCOUNT 0

END;
GO