以SQL Server2012提供的offset ..rows fetch next ..rows only为例

e.g.

表名：Tab1----------------------------------ID	Name1	tblAttributeGroupDetail2	tblAttributeGroup3	tblAttribute.......50	tblBRItemTypeAppliesTo51	tblBRItemProperties52	tblBRItem53	tblBRBusinessRule54	Test

--创建分页存储过程 rTabByCondition

USE [ExampleDB]GOif OBJECT_ID('rTabByCondition','P') is not null  drop procedure rTabByConditionGOcreate procedure [dbo].[rTabByCondition](@PageCount int=1			--页数,@PageSize int=10			--页显示记录数,@Rowcount int=0 output		--总记录数)asset nocount on;declare @Rows int;select * from dbo.Tab1 order by ID  offset (@PageCount-1)*@PageSize rows fetch next @PageSize rows onlyset @Rows=@@ROWCOUNTselect @Rowcount=count(*) from dbo.Tab1;return @Rowsgodeclare @i int,@j intexec @i=[rTabByCondition] @PageCount=6,@PageSize=10,@Rowcount=@j outputselect @i as "@Rowcount",@j as "Return_Value"go

显示结果：

--打开Visual Studio—创建项目—选择【控制台应用程序】

#region Directivesusing System;using System.Collections.Generic;using System.Linq;using System.Text;using System.Threading.Tasks;using System.Data;using System.Data.SqlClient;#endregionnamespace SQLStoredProcedure2{    class Program    {        static void Main(string[] args)        {            SqlConnection thisConnection = new SqlConnection(@"Server=(Local)\SQL16;Integrated Security=True;Database=ExampleDB");            thisConnection.Open();            SqlCommand thisCommend = thisConnection.CreateCommand();            thisCommend.CommandType = CommandType.StoredProcedure;            thisCommend.CommandText = "rTabByCondition";            thisCommend.Parameters.AddWithValue("@PageCount", "6");//页数            thisCommend.Parameters.AddWithValue("@PageSize", "10");//页显示记录数            SqlParameter paraOut = thisCommend.Parameters.Add("@Rowcount", SqlDbType.Int);//输出参数定义            paraOut.Direction = ParameterDirection.Output;            SqlParameter paraRet = thisCommend.Parameters.Add("return_value", SqlDbType.Int);//返回值            paraRet.Direction = ParameterDirection.ReturnValue;            SqlDataReader thisReader = thisCommend.ExecuteReader();            while (thisReader.Read())            {                Console.WriteLine("ID:{0}\tName:{1}", thisReader[0], thisReader[1]);            }            thisReader.Close();            thisConnection.Close();            Console.WriteLine("Rows:{0};\tReturn_Value:{1};", paraOut.Value, paraRet.Value);            Console.WriteLine("Program finished,press Enter/Return to continue:");            Console.ReadLine();        }    }}

显示效果：