/*----------------------------
Author     :Nick.Liu
CreatedDate:2008-12-23

CopyRight:http://www.dreampea.com //使用请保留这里
功能:
1.实现查询分页功能
2.随机读取一页数据
输入参数说明：
@Source :数据源，可以是一个查询语句
@PageSize:一页大小，-1为查询所有页，-2为查询随机页
@RowIDOrderString :排序规则 格式：字段名 desc/asc
@CurrentPageIndex :当前页，由1开始

输出：
表1：当前页数据
表2：@Source 的行数
修改记录：

-----------------------------*/
CREATE Proc [dbo].[Proc_Pager]
@Source nvarchar(1000)
,@PageSize int
,@RowIDOrderString nvarchar(200)
,@CurrentPageIndex int

as
DECLARE @SQLString nvarchar(500);
declare @SqlQuery nvarchar(2000)
DECLARE @ParmDefinition nvarchar(500);
DECLARE @QueryParmDefinition nvarchar(500);
DECLARE @CountOut varchar(30);

SET @SQLString = N'SELECT @Count = count(1)
   FROM ('+@Source+') a'
SET @ParmDefinition = N'@Count int OUTPUT';
SET @QueryParmDefinition = N'@BeginIndexInner bigint,@EndIndexInner bigint';

EXECUTE sp_executesql @SQLString, @ParmDefinition, @Count=@CountOut OUTPUT;
declare @Pages int
set @Pages = @CountOut/@PageSize
if @CurrentPageIndex = -2
Begin
set @CurrentPageIndex = cast(@Pages*Rand() as int)
End

declare @BeginIndex  bigint
declare @EndIndex  bigint

set @BeginIndex = @PageSize*(@CurrentPageIndex-1)
set @EndIndex = @PageSize*@CurrentPageIndex

set @SqlQuery = 'select RowCnt,* from
                        (
                            select ROW_NUMBER() OVER(order by '+@RowIDOrderString+') as RowCnt
                            ,* from ('+@Source+') a
                        )a'
    if(@CurrentPageIndex != -1)
    Begin
             set @SqlQuery = @SqlQuery+ ' where RowCnt > @BeginIndexInner
                        and RowCnt <=@EndIndexInner'
    End
print @sqlQuery
EXECUTE sp_executesql @SqlQuery,@QueryParmDefinition,@BeginIndexInner= @BeginIndex,@EndIndexInner= @EndIndex
exec('select RowCnt=count(1) from ('+@Source+') a')