I was extremely excited about GridViews and DataGrids when I first started working with .NET. The thought of having controls with built in paging and sorting just blew me away. Needless to say, this excitement was short lived. By the end of my first project in .NET, I found that this built in sorting and paging had some major performance issues. It was too much data to send back and forth between the web server and the SQL server; not to mention that .NET just isn't as optimized to sort that much data as fast as SQL Server.
The alternative was to have SQL Server sort and page the data and only send back the data that was needed to the web server. After some research and some time to combine the ideas I found into my own solution I came up with:
CREATE PROCEDURE dbo.ReturnPage
(
@SelectStmt varchar(1000), --Select Statement with Where Clause
@OrderBy varchar(1000), --Order By Clause 'ASC' is required
@StartRow int, --First Row of Page
@EndRow int, --Last Row of Page
@totRows int output --Output: Total Rows in Set
)
AS
DECLARE @ColList varchar(2000)
DECLARE @WhereCls varchar(2000)
DECLARE @i int
DECLARE @i2 int
DECLARE @tmp varchar(1000)
DECLARE @dec varchar(1000)
DECLARE @f varchar(100)
DECLARE @d varchar(100)
DECLARE @Symbol char(2)
DECLARE @SQL varchar(4000)
DECLARE @Sort varchar(1000)
DECLARE @tmpSQL NVARCHAR(4000)
--Assign the total number of rows available to @totRows for output
SET @SQL = N'SELECT @totRows = COUNT(*) FROM (' + @SelectStmt + ') tbl'
SET @tmpSQL = '' + @SQL
EXEC sp_executesql @tmpSQL , N'@totRows int OUTPUT',
@totRows = @totRows OUTPUT
SET @Sort = @OrderBy + ', '
SET @dec = ''
SET @WhereCls = ''
SET @SQL = ''
SET @i = charindex(',' , @Sort)
--Pull apart the sort by clause for declarations
WHILE (@i != 0)
BEGIN
SET @tmp = LEFT(@Sort,@i-1)
SET @i2 = charindex(' ', @tmp)
SET @f = ltrim(rtrim(LEFT(@tmp,@i2-1)))
SET @d = ltrim(rtrim(substring(@tmp,@i2+1,100)))
SET @Sort = rtrim(ltrim(substring(@Sort,@i+1,100)))
SET @i = charindex(',', @Sort)
SET @symbol = case when @d = 'ASC' then '>' else '< ' end +
case when @i=0 then '=' else '' end
SET @dec = @dec + 'declare @' + @f + ' sql_variant; '
SET @ColList = isnull(REPLACE(REPLACE(@colList,'>','='),'< ','=') +
' and ','') + @f + @Symbol + ' @' + @f
SET @WhereCls = @WhereCls + ' OR (' + @ColList + ') '
SET @SQL = @SQL + ', @' + @f + '= ' + @f
END
--Put together Transact SQL for final output
SET @SQL = @dec + ' ' +
'SET ROWCOUNT ' + convert(varchar(10), @StartRow) + '; ' +
'SELECT ' + substring(@SQL,3,7000) + ' from ('
+ @SelectStmt + ') a ORDER BY ' + @OrderBy + '; ' +
'SET ROWCOUNT ' +
convert(varchar(10), 1 + @EndRow - @StartRow) + '; ' +
'select * from (' + @SelectStmt + ') a WHERE ' +
substring(@WhereCls,4,7000) + ' ORDER BY ' + @OrderBy +
'; SET ROWCOUNT 0;'
EXEC(@SQL)
GO
What's it doing?
The procedure works by turning a simple query like:
SELECT col1, col2, col3, col4 FROM TABLE ORDER BY col1 ASC
and turning it into the following to add the paging.
DECLARE @col1 sql_variant
SET ROWCOUNT = @StartRow
SELECT @col1 = col1 FROM TABLE ORDER BY col1 ASC
SET ROWCOUNT = (@EndRow - @StartRow) + 1
SELECT col1, col2, col3, col4 FROM tbl WHERE col1 > @col1
ORDER BY col1 ASC
Simple enough right?
Disclaimer
While this procedure does work with more complex queries containing SUB-SELECTS, JOINS and WHERE clauses, it does have its limitations. GROUP BY clauses do not work in this version. Maybe in the future.
I wouldn't suggest calling this procedure directly as that would mean you're building your query in-code, and probably not doing necessary escaping to prevent security issues. Creating a stored procedure that builds the original query from passed in WHERE and ORDER BY Parameters and then calls the ReturnPage procedure will be much more efficient and secure.
I hope to post a complete sample that will illustrate the changes to the .NET code and the wrapping procedure soon.
andrew:
hey mike -- thanks for the reply, let me clarify what i mean.... I know that PHP fu...
nick:
Hi Jeff, Thanks for the heads up on the link. It's all fixed now and you should...
Jeff:
I would love to try your plugin, but the download link appears to be dead again. Ca...
mike:
@Denise: 1. The image is selected randomly each time the code is run. So normally ...
andrew:
hey -- great plugin and would like to use on several different pages, not just the ...