SQL语句： create PROCEDURE [dbo].[GetPageDataOutRowNumber](@tn nvarchar(30),--表名称@idn nvarchar(20),--表主键名称@pi int = 1,--当前页数@ps  int = 7,--每页大小@wh nvarchar(255) = '',--wehre查询条件@oby nvarchar(255) = '',--orderby 排序@rc int output,--总行数(传出参数)@pc int output--总页数(传出参数))ASDECLARE @sql NVARCHAR(225)='',@sqlCount NVARCHAR(225)=''--1.计算总行数和总页数SET @sqlCount = 'SELECT @rc=COUNT(['+@idn+']),@pc=CEILING((COUNT('+@idn+')+0.0)/'+ CAST(@ps AS VARCHAR)+') FROM ' + @tnIF LEN(@wh)>1    set @sqlCount=@sqlCount+' WHERE '+@whprint @sqlCountEXEC SP_EXECUTESQL @sqlCount,N'@rc INT OUTPUT,@pc INT OUTPUT',@rc OUTPUT,@pc OUTPUT--2.分页--2.1如果是第一页，则直接查询IF @pi = 1BEGIN    SET @sql='SELECT TOP '+str(@ps) +' * FROM '+@tn    IF LEN(@wh)>1        set @sql=@sql+' WHERE '+@wh    IF LEN(@oby)>1            SET @sql=@sql+' order by ' +@obyEXEC(@sql)ENDELSE--2.2如果不是第一页，则拼接查询语句BEGIN    SET NOCOUNT ON    SET @sql='SELECT * FROM (select row_number() over(order by '    IF LEN(@oby)>1        set @sql=@sql + @oby+') as rowNum,* from '+@tn    else        set @sql=@sql + @idn+') as rowNum,* from '+@tn    IF LEN(@wh)>1        set @sql=@sql+' where '+@wh    set @sql=@sql+')as temp where rowNum>'+str(@ps * (@pi-1))+' and rowNum<='+str(@ps*@pi)    print @sql    EXEC(@sql)    SET NOCOUNT OFFEND

 

 

 

--测试语句

declare @rc int,@pc intexec [GetPageDataOutRowNumber] 'Ams_Area','ar_id',2,5,'',' ar_id desc',@rc output,@pc outputselect @rc,@pc

 

--C#调用

 

private void getdataByfenye(HttpContext context)        {            int pageSize = int.Parse(context.Request["rows"].ToString());            int page = int.Parse(context.Request["page"].ToString());            string strCon = @"Server=192.168.10.237;database=KM;uid=sa;pwd=Password01!;";            SqlConnection con = new SqlConnection(strCon);            SqlCommand com = new SqlCommand("GetPageDataOutRowNumber", con);            com.CommandType = CommandType.StoredProcedure;            com.Parameters.Add(new SqlParameter("@tn", "T_KNOWLEDGE_EXPERT"));            com.Parameters.Add(new SqlParameter("@idn", "OID"));            com.Parameters.Add(new SqlParameter("@pi", page));            com.Parameters.Add(new SqlParameter("@ps", pageSize));            string strWhere = "";            string strOby = "";            string strExpert = context.Request["expert"] == null ? "" : context.Request["expert"].ToString();            string strDeptno = context.Request["deptno"] == null ? "" : context.Request["deptno"].ToString();            if (!string.IsNullOrEmpty(strExpert))            {                strWhere += " EXPERT = '" + strExpert + "' and ";            }            if (!string.IsNullOrEmpty(strDeptno))            {                strWhere += " DEPARTMENT like '%" + strDeptno + "%' and ";            }            if (!string.IsNullOrEmpty(strWhere))            {                strWhere = strWhere.Substring(0, strWhere.LastIndexOf("and"));            }            com.Parameters.Add(new SqlParameter("@wh", strWhere));            com.Parameters.Add(new SqlParameter("@oby", strOby));            SqlParameter param = new SqlParameter("@rc", SqlDbType.Int);            param.Direction = ParameterDirection.Output;            com.Parameters.Add(param);            SqlParameter param1 = new SqlParameter("@pc", SqlDbType.Int);            param1.Direction = ParameterDirection.Output;            com.Parameters.Add(param1);            com.CommandTimeout = 180;            SqlDataAdapter sda = new SqlDataAdapter();            sda.SelectCommand = com;            con.Open();            DataSet ds = new DataSet();            sda.Fill(ds);            con.Close();            object o = com.Parameters["@rc"].Value;            int total = (o == null || o == DBNull.Value) ? 0 : Convert.ToInt32(o);            object b = com.Parameters["@pc"].Value;            int pageCount = (b == null || o == DBNull.Value) ? 0 : Convert.ToInt32(b);            List
      
        lst = new List
       
        ();            KnowledgeExpert ke = new KnowledgeExpert();            foreach (DataRow dr in ds.Tables[0].Rows)            {                ke = new KnowledgeExpert();                ke.CREATED_BY = dr["CREATED_BY"] == null ? "" : dr["CREATED_BY"].ToString();                ke.CREATED_TIME = dr["CREATED_TIME"] == null ? "" : dr["CREATED_TIME"].ToString();                ke.DEPARTMENT = dr["DEPARTMENT"] == null ? "" : dr["DEPARTMENT"].ToString();                ke.DESCRIPTION = dr["DESCRIPTION"] == null ? "" : dr["DESCRIPTION"].ToString();                ke.EXPERT = dr["EXPERT"] == null ? "" : dr["EXPERT"].ToString();                ke.OID = dr["OID"].ToString();                lst.Add(ke);            }            JavaScriptSerializer jss = new JavaScriptSerializer();            String str = jss.Serialize(lst);            String str2 = "{\"total\":" + total + ",\"rows\":" + str + "}";            //Thread.Sleep(3000);            context.Response.Write(str2);        }