Ok, maybe it is not praise, but CodeBetter.com is usually a very .NET and Microsoft centric site/blog. These types typically won’t even mention a product if it isn’t made by MS or support an MS product (Studio plugins).
It was nice to see Karl post on Paging Data – MySQL > Microsoft. It was nice, and I agree the LIMIT clause of MySQL is VERY nice, short, and sweet. PostgreSQL achieves the same thing using LIMIT and OFFSET. On our SQL 2000 Server Databases we sometimes use a stored procedure which I found on the internet somewhere a few months ago. (I think from http://www.vbdotnetheaven.com/Code/Sept2003/2173.asp)
CREATE PROC dbo.GetPagedData
@pageSize int,
@tablename varchar(100) ,
@PrimaryKey varchar(50) ,
@CurrentPage int ,
@WhereClause varchar(250)
AS
if(@WhereClause IS NULL or @WhereClause='') Set @WhereClause=' 1=1 '
Declare @sql nvarchar(4000)
declare @numrecs int
Set @numrecs=@pageSize*@currentPage
set @sql='SELECT TOP ' + cast(@pageSize as varchar(5))+' * FROM ' +@tablename
Set @sql =@sql + ' WHERE '+cast(@PrimaryKey as varchar(50))+ ' NOT IN (SELECT TOP '
set @sql=@sql+ cast(@numrecs as varchar(5))
set @sql=@Sql+ ' ' +@primarykey +' FROM ' +@tableName + ' WHERE '+@whereClause +
' ORDER BY ' +cast(@primarykey as varchar(50)) +' ) '
set @Sql=@sql + ' AND '+ @whereClause
Set @Sql=@Sql + ' ORDER BY '+cast(@primarykey as varchar(50))
--print @sql
EXEC sp_executeSql @sql
Set @sql='Select count(*) FROM ' +@tablename + ' WHERE ' +@whereclause
EXEC sp_executeSql @sql
You can see that this generates SQL in the form of
SELECT TOP ${pagesize} *
FROM table
WHERE somekey NOT IN (
SELECT TOP ${pagesize times currentpage} somekey FROM table WHERE query ORDER BY somekey )
AND query ORDER BY somekey
I find that VERY readable. Not as readable as the MySQL and PostgreSQL options, but it is more readable than the SQL2005 method Maybe it doesn’t work or doesn’t perform well, but we use it without issues.
