我也抛砖引玉——通用分页存储过程（SqlServer2005）

以前用的sqlserver2000，写了一个通用分页存储过程，传进去一个sql语句、页码、页大小就能进行分页，最近公司升级sqlserver2005，就把以前的改了改，从执行计划和统计的各项计数来看性能比以前好了。

正好今天有人探讨这个话题，我就也贴上来分享一下，欢迎批评指正，要是能提些改进建议那最好不过了。

 

CREATE PROCEDURE [dbo].[PageView]
    @select VARCHAR(max),
    @CurrentPage INT,
    @PageSize INT
AS
BEGIN
    declare @sql NVARCHAR(max)
    DECLARE @RecordCurrent INT
    DECLARE @PageCount INT
    DECLARE @RecordCount INT
    SET NOCOUNT ON
    set @sql='select @RecordCount=count(*) from ('+@select+') a'
    exec sp_executesql @sql,N'@RecordCount int output',@RecordCount output
    SET @PageCount=(@RecordCount+@PageSize-1)/@PageSize
    IF ISNULL(@CurrentPage,0)<1
        SET @CurrentPage=1
    ELSE if ISNULL(@CurrentPage,0)>@PageCount
        SET @CurrentPage=@PageCount
    SELECT @CurrentPage AS CurrentPage,@RecordCount AS RecordCount,@PageSize AS PageSize,@PageCount AS PageCount
    set @sql='select * from ('+@select+') a where rownumber between '+cast((@CurrentPage-1)*@PageSize+1 as varchar)+' and '+cast(@CurrentPage*@PageSize as varchar)
    exec (@sql)
END

 

 

调用

 

exec PageView 'select userid,username,row_number() over(order by userid desc) as rownumber from Users',2,10

 缺点很显然'select userid,username,row_number() over(order by userid desc) as rownumber from Users',这里要提供rownumber 列