基于SQL Server 2005新特性的分页存储过程

/*

名称:PagingRecord 

作用:按任意字段进行排序分页 

作者:谢忠领 

时间:2007-2-1 

声明:此代码你可以无偿使用及转载,但在转载时,请勿移称本文字声明 

*/ 

 

CREATE   PROCEDURE [dbo].[PagingRecord]

    ( 

    @PageIndex int,--页号,从0开始

    @PageSize int,--页尺寸

    @OrderField varchar(100),--排序字段及类型(多个条件用逗号分开)如：JobID DESC,Checkintime

    @TableName varchar(100),--表名或视图表 

    @StrWhere varchar(2000),--条件 

    @FieldList varchar(2000),--欲选择字段列表      

    @DoCount  AS bit=1-- 0值返回记录总数, 非 0 值则返回记录

    ) 

AS 

 

BEGIN TRAN

 

DECLARE @SqlQuery varchar(4000)

 

IF @DoCount<>0

    Goto GetCount

Else

    Goto GetSearch

 

 

 

GetCount:--返回记录总数

    DECLARE @SearchSql AS Nvarchar(4000)

    SET @SearchSql= 'SELECT Count(*) AS Total FROM '+@TableName+' WHERE '+@StrWhere

    exec sp_executesql @SearchSql

    --print @SearchSql 

COMMIT TRAN

    return

 

GetSearch:

 

    SET @SqlQuery='SELECT '+@FieldList+'

    FROM (SELECT row_number() over(ORDER BY '+@OrderField+') as rownum, 

            '+@FieldList+'

          FROM '+@TableName+' WHERE '+@StrWhere +') as temp

    WHERE rownum BETWEEN ('+cast(@PageIndex as varchar)+'-1)*'+cast(@PageSize as varchar)+'+1 and '+cast(@PageIndex as varchar)+'*'+cast(@PageSize as varchar) + ' ORDER BY '+@OrderField

    --print @SqlQuery

    SET NOCOUNT ON

    execute(@SqlQuery)

    SET NOCOUNT OFF

 

 

COMMIT TRAN