[转]SQL存储过程分页完美版

--drop procedure p_page
--go

create procedure p_page

(
@Tables    varchar(1000), --表名如testtable
@PrimaryKey    varchar(100),--表的主键,必须唯一性
@Sort    varchar(200)    =    NULL,--排序字段如f_Name asc或f_name desc(注意只能有一个排序字段)
@CurrentPage    int    =    1,--当前页
@PageSize    int    =    10,---每页大小
@Fields    varchar(1000)    =    '*',--显示的字段列表
@Filter    varchar(1000)    =    NULL,--条件语句,不加where,如 f_id>3
@Group    varchar(1000)    =    NULL,--分组字段
@TotalPage int     output --返回总页数
)

WITH ENCRYPTION ---加密存储

AS



     SET NOCOUNT ON
     Declare @intResult Int    

     Begin Tran

     DECLARE @sql nvarchar(4000)
    
     if @Filter is null or @Filter=''  
         set @Sql = 'select @intResult = count(' + @PrimaryKey + ') from ' +   @Tables
         else
         set @Sql = 'select @intResult = count(' + @PrimaryKey + ') from ' +   @Tables + ' where + ' + @Filter    

     EXEC sp_executesql @sql,N'@intResult int OUTPUT',@intResult OUTPUT--计算总记录数
     select @TotalPage=CEILING((@intResult+0.0)/@PageSize)--计算总页数
    
    
     IF    @Sort    IS    NULL    or    @Sort    =    ''
     SET    @Sort    =    @PrimaryKey

     DECLARE    @SortTable    varchar(100)
     DECLARE    @SortName    varchar(100)
     DECLARE    @strSortColumn    varchar(200)
     DECLARE    @operator    char(2)
     DECLARE    @type    varchar(100)
     DECLARE    @prec    int
    
     IF    CHARINDEX('DESC',@Sort)>0
       BEGIN
         SET    @strSortColumn    =    REPLACE(@Sort,    'DESC',    '')
         SET    @operator    =    '<='
       END
     ELSE
          IF    CHARINDEX('ASC',    @Sort)    >    0
            BEGIN  
               SET    @strSortColumn    =    REPLACE(@Sort,    'ASC',    '')
               SET    @operator    =    '>='
            END
        
         ELSE    
            BEGIN
             SET    @strSortColumn   = @SORT
             SET    @operator    =    '>='
            END
    
     IF    CHARINDEX('.',    @strSortColumn)    >    0
         BEGIN
           SET    @SortTable    =    SUBSTRING(@strSortColumn,    0,    CHARINDEX('.',@strSortColumn))
           SET    @SortName    =    SUBSTRING(@strSortColumn,    CHARINDEX('.',@strSortColumn)    +    1,    LEN(@strSortColumn))
         END
     ELSE
         BEGIN
           SET    @SortTable    =    @Tables
           SET    @SortName    =    @strSortColumn
         END
    
     Select    @type=t.name,    @prec=c.prec
     FROM    sysobjects    o   
     JOIN    syscolumns    c    on    o.id=c.id
     JOIN    systypes    t    on    c.xusertype=t.xusertype
     Where    o.name    =    @SortTable    AND    c.name    =    @SortName
    
     IF    CHARINDEX('char',    @type)    >    0
           SET    @type    =    @type    +    '('    +    CAST(@prec    AS    varchar)    +    ')'
    
     DECLARE    @strPageSize    varchar(50)
     DECLARE    @strStartRow    varchar(50)
     DECLARE    @strFilter    varchar(1000)
     DECLARE    @strSimpleFilter    varchar(1000)
     DECLARE    @strGroup    varchar(1000)
    
     IF    @CurrentPage    <    1
     SET    @CurrentPage    =    1
    
     SET    @strPageSize    =    CAST(@PageSize    AS    varchar(50))
     SET    @strStartRow    =    CAST(((@CurrentPage    -    1)*@PageSize    +    1)    AS    varchar(50))
    
     IF    @Filter    IS    NOT    NULL    AND    @Filter    !=    ''
       BEGIN
         SET    @strFilter    =    '    Where    '    +    @Filter    +    '    '
         SET    @strSimpleFilter    =    '    AND    '    +    @Filter    +    '    '
       END
     ELSE
       BEGIN
         SET    @strSimpleFilter    =    ''
         SET    @strFilter    =    ''
       END
    
     IF    @Group    IS    NOT    NULL    AND    @Group    !=    ''
       SET    @strGroup    =    '    GROUP    BY    '    +    @Group    +    '    '
     ELSE
       SET    @strGroup    =    ''
    
     set @sql = 'DECLARE    @SortColumn    '    +    @type    +    '
     SET    ROWCOUNT    '    +    @strStartRow    +    '
     Select    @SortColumn='    +    @strSortColumn    +    '    FROM    '    +    @Tables    +    @strFilter    +    '    '    +    @strGroup    +    '    orDER    BY    '    +    @Sort    +    '
     SET    ROWCOUNT    '    +    @strPageSize    +    '
     Select    '    +    @Fields    +    '    FROM    '    +    @Tables    +    '    Where    '    +    @strSortColumn    +    @operator    +    '    @SortColumn    '    +    @strSimpleFilter    +    '    '    +    @strGroup    +    '    orDER    BY    '    +    @Sort    +    '
     '
    
     exec(@sql)
     --print @sql

-------------------------------------------------------------------------------------------------

     If @@Error <> 0
        Begin
            RollBack Tran
            Return -1
        End
     Else
        Begin
            Commit Tran
            Return @intResult ---返回记录总数
        End

GO

 

**//// <summary>
     /// 绑定数据
     /// </summary>
     protected void cfBindData(string sqlwhere)
     {

         //分页开始
         SqlParameter[] myParms = new SqlParameter[10];

         myParms[0] = new SqlParameter("@Tables", SqlDbType.VarChar, 50);
         myParms[0].Value = "cf_Product";

         myParms[1] = new SqlParameter("@PrimaryKey", SqlDbType.VarChar, 50);
         myParms[1].Value = "cf_id";

         myParms[2] = new SqlParameter("@Sort", SqlDbType.VarChar, 50);
         myParms[2].Value = "cf_id desc";

         myParms[3] = new SqlParameter("@CurrentPage", SqlDbType.Int, 4);
         myParms[3].Value = Convert.ToInt16(pageIndex);

         myParms[4] = new SqlParameter("@pageSize", SqlDbType.Int, 4);
         myParms[4].Value = pageSize;

         myParms[5] = new SqlParameter("@Fields", SqlDbType.VarChar, 500);
         myParms[5].Value = "* ";

         myParms[6] = new SqlParameter("@Filter", SqlDbType.VarChar, 500);
         myParms[6].Value = sqlwhere;

         myParms[7] = new SqlParameter("@Group", SqlDbType.VarChar, 50);
         myParms[7].Value = "";

         myParms[8] = new SqlParameter("@totalPage", SqlDbType.Int, 4);
         myParms[8].Direction = ParameterDirection.Output;

         myParms[9] = new SqlParameter("@totalRecord", SqlDbType.Int, 4);
         myParms[9].Direction = ParameterDirection.ReturnValue;

         SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["connStr"].ToString());
         conn.Open();
         SqlCommand sc = new SqlCommand("up_page", conn);
         sc.CommandType = CommandType.StoredProcedure;
         foreach (SqlParameter parameter in myParms)
         {
             sc.Parameters.Add(parameter);
         }
         sc.ExecuteNonQuery();

         totalPage = Convert.ToInt16(myParms[8].Value);
         totalRecord = Convert.ToInt16(myParms[9].Value);

         SqlDataReader sdr = sc.ExecuteReader();
         GV1.DataSourceID = null;
         GV1.DataSource = sdr;
         GV1.DataBind();

         sc.Dispose();
         conn.Close();
         conn.Dispose();
         //分页结束
     }

lblPage.Text = tl.ShowPage(totalRecord, totalPage, 11, pageSize, Convert.ToInt16(pageIndex), "none", "个", "highlight", true);