分页存储过程---repeater分页

CREATE PROCEDURE [dbo].[SP_Page]
(
@Sql nvarchar(1024),   --查询语句
@Sort nvarchar(100) = '',    --排序字段
@PageSize int = 20,    --分页大小
@PageIndex int = 1,    --分页索引
@TotalCount int = 0 output --总数   
)
AS

-- 值默认值
if (IsNUll(@PageSize,0)=0)
    Set @PageSize=20
if (IsNull(@PageIndex,0)=0)
    Set @PageIndex=1

set nocount on
/*声明查询字符串*/
declare @strSQL nvarchar(4000)

set @strSQL = ' select @TotalCount=count(*) from ('+@Sql+') as t '

/*取得查询结果总数*/
exec sp_executesql
@strSQL,
N'@TotalCount int=0 OUTPUT',
@TotalCount=@TotalCount OUTPUT

declare @ItemCount int
declare @_PageIndex int

set @_PageIndex = @PageIndex; --索引从1开始
--set @_PageIndex = @PageIndex + 1; --索引从0开始

/*确定搜索边界*/
set @ItemCount = @TotalCount - @PageSize * @_PageIndex

if(@ItemCount < 0)
    set @ItemCount = @ItemCount + @PageSize
else
    set @ItemCount = @PageSize

if(@ItemCount < 0) return 1

if(@Sort != '')
begin
    /*声明排序变量*/
    declare @IndexSort1 nvarchar(50), @IndexSort2 nvarchar(50), @Sort1 nvarchar(50), @Sort2 nvarchar(50)
   
    SET @Sort1 = @Sort
    SET @Sort2 = Replace(Replace(Replace(@Sort, 'DESC', '@SORT'), 'ASC', 'DESC'), '@SORT', 'ASC')

    set @strSQL = 'SELECT * FROM
    (SELECT TOP ' + STR(@ItemCount) + ' * FROM
    (SELECT TOP ' + STR(@PageSize * @_PageIndex) + ' * FROM
    ('+@Sql+') AS t0
    ORDER BY '+@Sort1 +') AS t1
    ORDER BY '+@Sort2 +') AS t2
    ORDER BY ' +@Sort
end
else
begin
    set @strSQL = 'SELECT * FROM
    (SELECT TOP ' + STR(@ItemCount) + ' * FROM
    (SELECT TOP ' + STR(@PageSize * @_PageIndex) + ' * FROM
    ('+@Sql+') As t0)
    aS t1)
    AS t2'
end

exec sp_executesql
@strSQL

转载于:https://www.cnblogs.com/houweidong/archive/2013/03/24/2978127.html