我常用的分页存储过程

SQL 分页存储过程：

CREATE PROCEDURE [dbo].[ProcCustomPage]
(
    @tbname     nvarchar(100),             --要分页显示的表名
    @FieldKey   nvarchar(1000),            --用于定位记录的主键(惟一键)字段,可以是逗号分隔的多个字段
    @PageCurrent int=1,                    --要显示的页码
    @PageSize   int=10,                    --每页的大小(记录数)
    @FieldShow nvarchar(1000)='',          --以逗号分隔的要显示的字段列表,如果不指定,则显示所有字段
    @FieldOrder nvarchar(1000)='',         --以逗号分隔的排序字段列表,可以指定在字段后面指定DESC/ASC
    @WhereString    nvarchar(1000)='',     --查询条件
    @RecordCount int OUTPUT                --总记录数
)
AS
SET NOCOUNT ON

--分页字段检查
IF ISNULL(@FieldKey,N'')=''
BEGIN
    RAISERROR(N'分页处理需要主键（或者惟一键）',1,16)
    RETURN
END

--其他参数检查及规范
IF ISNULL(@PageCurrent,0)<1 SET @PageCurrent=1
IF ISNULL(@PageSize,0)<1 SET @PageSize=10
IF ISNULL(@FieldShow,N'')=N'' SET @FieldShow=N'*'
IF ISNULL(@FieldOrder,N'')=N''
    SET @FieldOrder=N''
ELSE
    SET @FieldOrder=N'ORDER BY '+LTRIM(@FieldOrder)
IF ISNULL(@WhereString,N'')=N''
    SET @WhereString=N''
ELSE
    SET @WhereString=N'WHERE ('+@WhereString+N')'

--如果@RecordCount为NULL值,则计算总页数(这样设计可以只在第一次计算总页数,以后调用时,
--把总页数传回给存储过程,避免再次计算总页数,对于不想计算总页数的处理而言,可以给@RecordCount赋值)
IF @RecordCount IS NULL
BEGIN
    DECLARE @sql nvarchar(4000)
    SET @sql=N'SELECT @RecordCount=COUNT(*)'
        +N' FROM '+@tbname
        +N' '+@WhereString
    EXEC sp_executesql @sql,N'@RecordCount int OUTPUT',@RecordCount OUTPUT
END

--计算分页显示的TOPN值
DECLARE @TopN varchar(20),@TopN1 varchar(20)
SELECT @TopN=@PageSize,
    @TopN1=(@PageCurrent-1)*@PageSize

--第一页直接显示
IF @PageCurrent=1
    EXEC(N'SELECT TOP '+@TopN
        +N' '+@FieldShow
        +N' FROM '+@tbname
        +N' '+@WhereString
        +N' '+@FieldOrder)
ELSE
BEGIN
    --处理别名
    IF @FieldShow=N'*'
        SET @FieldShow=N'a.*'

    --生成主键(惟一键)处理条件
    DECLARE @Where1 nvarchar(4000),@Where2 nvarchar(4000),
        @s nvarchar(1000),@Field sysname
    SELECT @Where1=N'',@Where2=N'',@s=@FieldKey
    WHILE CHARINDEX(N',',@s)>0
        SELECT @Field=LEFT(@s,CHARINDEX(N',',@s)-1),
            @s=STUFF(@s,1,CHARINDEX(N',',@s),N''),
            @Where1=@Where1+N' AND a.'+@Field+N'=b.'+@Field,
            @Where2=@Where2+N' AND b.'+@Field+N' IS NULL',
            @WhereString=REPLACE(@WhereString,@Field,N'a.'+@Field),
            @FieldOrder=REPLACE(@FieldOrder,@Field,N'a.'+@Field),
            @FieldShow=REPLACE(@FieldShow,@Field,N'a.'+@Field)
    SELECT @WhereString=REPLACE(@WhereString,@s,N'a.'+@s),
        @FieldOrder=REPLACE(@FieldOrder,@s,N'a.'+@s),
        @FieldShow=REPLACE(@FieldShow,@s,N'a.'+@s),
        @Where1=STUFF(@Where1+N' AND a.'+@s+N'=b.'+@s,1,5,N''),    
        @Where2=CASE
            WHEN @WhereString='' THEN N'WHERE ('
            ELSE @WhereString+N' AND ('
            END+N'b.'+@s+N' IS NULL'+@Where2+N')'

    --执行查询
    EXEC(N'SELECT TOP '+@TopN
        +N' '+@FieldShow
        +N' FROM '+@tbname
        +N' a LEFT JOIN(SELECT TOP '+@TopN1
        +N' '+@FieldKey
        +N' FROM '+@tbname
        +N' a '+@WhereString
        +N' '+@FieldOrder
        +N')b ON '+@Where1
        +N' '+@Where2
        +N' '+@FieldOrder)
END
GO

 

对此分页存储过程的C#封装代码：

/// <summary>
/// 分页获取数据列表 适用于SQL2000
/// </summary>
/// <param name="connectionString">连接字符串</param>
/// <param name="fieldlist">查找的字段</param>
/// <param name="tablename">表名</param>
/// <param name="where">查询条件</param>
/// <param name="orderfield">排序字段</param>
/// <param name="key">主键</param>
/// <param name="pageindex">页索引</param>
/// <param name="pagesize">每页记录数</param>
/// <param name="ordertype">排序方式 0=ASC 1=DESC</param>
/// <param name="recordcount">总记录数</param>
/// <returns></returns>
public static DataTable GetPagerDataForSQL2k(string connectionString, string fieldlist, 
         string tablename, string where, string orderfield, string key, int pageindex, int pagesize)
{
    string cmd = "ProcCustomPage";
    SqlParameter[] para = new SqlParameter[8];
    para[0] = new SqlParameter("@tbname", tablename);
    para[1] = new SqlParameter("@FieldKey", key);
    para[2] = new SqlParameter("@WhereString", where);
    para[3] = new SqlParameter("@PageSize", pagesize);
    para[4] = new SqlParameter("@PageCurrent", pageindex);
    para[5] = new SqlParameter("@FieldOrder", orderfield);
    para[6] = new SqlParameter("@FieldShow", fieldlist);
    para[7] = new SqlParameter("@RecordCount", SqlDbType.Int);

    return SqlHelper.ExecuteDataset(connectionString, CommandType.StoredProcedure, cmd, para).Tables[0];

}

使用注意事项：

表中的字段，id为主键，如：id,userid,companyid.... 这样在分页时会有问题哦。应将id改为 productid 类似于这样的xxxid 这样在分页就OK，为什么要这样呢？因为如将主键设为id,分页存储过程会将 userid 替换为usera.id 在执行时就会出错了。如果字段名称中不包括主键的字符就可以。