USE [Northwind_cn]
GO
/****** Object:  StoredProcedure [dbo].[PS_GetPageData]    Script Date: 04/21/2012 02:26:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:  <zsm,,Name>
-- Create date: <Create Date,2012-04-20,>
-- Description: <Description，用于进行分页查询的存储过程，能够执行表或者视图或者一个带Select的查询结果的查询,>
-- =============================================
ALTER PROCEDURE [dbo].[PS_GetPageData]
-- Add the parameters for the stored procedure here
@TVName nvarchar(1000), --表或视图或查询语句
@IsSelectSQL bit, --@TVName是否带select的查询语句
@IsReturnRecord bit, --是否返回记录
@PageSize int,      --每页记录大小
@PageIndex int,  --要返回的页数
@WhereStr nvarchar(1000),--where后面的查询条件
@OrderStr nvarchar(400), ---order by 后面用于排序的子句
@FieldStr nvarchar(1000),--要返回的字段名
@AllRecordCount int output,    --返回本次查询能够查到的所有记录数目
@RecordCount int output,    --本次查到返回的记录数目
@PageCount int output,      --返回本次查询能够返回的页数
@RMessage nvarchar(max) output  --返回执行结果信息
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
declare @SQLCount nvarchar(max);
declare @SQLSelect nvarchar(max);
declare @SQLstr nvarchar(max);
declare @SQLOrderStr nvarchar(450);
if(@IsSelectSQL=1)--表明是一个查询语句
begin
set @TVName=N'('+@TVName+') ttb '
end
if(RTRIM(LTRIM(@WhereStr))<>'')--没有输入查询条件
begin
set @SQLCount = 'select @AllRecordCount = count(*) from '+@TVName +' where '+@WhereStr
end
else
begin
set @SQLCount = 'select @AllRecordCount = count(*) from '+@TVName
end
--执行可以多次重复使用或动态生成的 Transact-SQL 语句或批处理。Transact-SQL 语句或批处理可以包含嵌入参数。
exec sp_executesql @SQLCount, N'@AllRecordCount int out', @AllRecordCount output
--计算页数
if(@AllRecordCount%@PageSize>0)
begin
set @PageCount=@AllRecordCount/@PageSize + 1
end
else
begin
set @PageCount=@AllRecordCount/@PageSize
end
if(@PageIndex>@PageCount or @PageIndex<=0 or @PageSize<=0)--要求的页超出总页面范围
begin
set @RecordCount=0
set @RMessage='Out of page or bad size'
return
end
--计算本次能够返回的行数
if(@AllRecordCount=0)--如果符合记录数为0
begin
set @RecordCount=0
end
else
begin
if(@AllRecordCount%@PageSize=0)--如果符合记录数能被页数整除
begin
set @RecordCount=@PageSize;
end
else
begin
if(@PageIndex = @PageCount)--如果符合记录数不能能被页数整除，并且是最后一页
begin
set @RecordCount=@AllRecordCount%@PageSize;
end
else
be
gin
set @RecordCount=@PageSize; --如果不能整除并且不在最后一页，返回页记录数 
end
end
end
if(@IsReturnRecord=0)
begin
set @RMessage=@SQLCount
return
end
sql sever 2008if(Rtrim(LTRIM(@OrderStr))<>'')--排序字段为空
begin
set @SQLOrderStr = N' order by '+@OrderStr
end
else
begin
set @RMessage='No Order Field'
return
end
declare @StartNums varchar(20)
declare @EndNums varchar(20)
set @StartNums = (@PageSize*(@PageIndex-1)) + 1
set @EndNums = (@PageSize*@PageIndex)
if(null=@FieldStr or RTRIM(LTRIM(@FieldStr))='')--没有输入字段
begin
set @FieldStr=N'*'
end
if(RTRIM(LTRIM(@WhereStr))<>'')--没有输入查询条件
begin
set @SQLSelect = @TVName + ' where '+@WhereStr
end
else
begin
set @SQLSelect = @TVName
end
set @SQLstr = '
with t_rowtable as ( select row_number() over(' + @SQLOrderStr + ' ) as row_number,' + @FieldStr + ' from ' + @SQLSelect + ' )
select ' + @FieldStr + ' from t_rowtable where row_number between ' + @StartNums + ' and ' + @EndNums + +' '+ @SQLOrderStr
set @RMessage = @SQLstr
exec(@SQLstr)   
END