GO
-- =============================================
-- Author: lenya
-- Create date: 2011/7/1
-- Description: 通用分页存储过程
-- =============================================
ALTER PROCEDURE [dbo].[pr_commonPagination]
@StrSql NVARCHAR(MAX) ,
@WhereClause VARCHAR(200) , --条件，可以为空，不加"where"
@SortClause VARCHAR(100) ,--排序字段，可以为空，不加"Order by"
@PageIndex INT ,--当前页码
@PageCount INT ,--每页条数
@TotalNum INT OUTPUT --总记录条数
AS
BEGIN
SET NOCOUNT ON;
DECLARE @ExeSql VARCHAR(MAX)
DECLARE @StrWhere VARCHAR(220)
DECLARE @OrderByStr VARCHAR(120)
DECLARE @StartRowIndex INT
DECLARE @EndRowIndex INT
CREATE TABLE #temp(rowscnt INT)
--set @StrSql = dbo.fn_convertSpecialCharacter(@StrSql) 特殊字符转义
--set @WhereClause = dbo.fn_convertSpecialCharacter(@StrWhere)
if @PageIndex<0
SET @PageIndex = 1
if @PageCount<0
SET @PageCount=10
SET @StartRowIndex = ( @PageIndex - 1 ) * @PageCount + 1
SET @EndRowIndex = @PageIndex * @PageCount
SET @StrWhere = ''
IF (@PageIndex = 0 and @PageCount=0) --如果页索引及页面条数均设置为0,则返回所有记录.
BEGIN
SET @StartRowIndex = 1
SET @EndRowIndex = 2147483647 --int最大值
END
/*--条件筛选--*/
IF len(LTRIM(@WhereClause))>0
BEGIN
SET @StrWhere = ' where ' + @WhereClause
END
/*--排序--*/
IF len(LTRIM(@SortClause))>0
SET @OrderByStr = ' order by ' + @SortClause
ELSE
SET @OrderByStr = ' order by (select 0) '--默认排序,ROW_NUMBER()需要它.
/*---查询总数-*/
EXEC('insert into #temp select count(*) from ( '+@StrSql+') as cnt '+ @StrWhere)
select @TotalNum = rowscnt from #temp
/*---执行查询-*/
SET @ExeSql = 'select row_number() over ('+@OrderByStr+')as rowIndex, * from ('+@StrSql+') as query' + @StrWhere
SET @ExeSql = 'select * from (' + @ExeSql + ') AS result where rowIndex between ' + CAST(@StartRowIndex AS VARCHAR)
+ ' And ' + CAST(@EndRowIndex AS VARCHAR) + ' ' + @OrderByStr
EXEC(@ExeSql)
drop table #temp
END
