以SQL Server2012提供的offset ..rows fetch next ..rows only为例
e.g.
表名:Tab1
----------------------------------
IDName
1tblAttributeGroupDetail
2tblAttributeGroup
3tblAttribute
.......
50tblBRItemTypeAppliesTo
51tblBRItemProperties
52tblBRItem
53tblBRBusinessRule
54Test--创建分页存储过程 rTabByCondition
USE [ExampleDB]
GO
if OBJECT_ID('rTabByCondition','P') is not null
drop procedure rTabByCondition
GO
create procedure [dbo].[rTabByCondition](
@PageCount int=1--页数
,@PageSize int=10--页显示记录数
,@Rowcount int=0 output--总记录数
)
as
set nocount on;
declare @Rows int;
select * from dbo.Tab1 order by ID offset (@PageCount-1)*@PageSize rows fetch next @PageSize rows only
set @Rows=@@ROWCOUNT
select @Rowcount=count(*) from dbo.Tab1;
return @Rows
go
declare @i int,@j int
exec @i=[rTabByCondition] @PageCount=6,@PageSize=10,@Rowcount=@j output
select @i as "@Rowcount",@j as "Return_Value"
go显示结果:
--打开Visual Studio—创建项目—选择【控制台应用程序】
#region Directives
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
#endregion
namespace 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();
}
}
}显示效果: