USE [Graduate]
GO
/****** 对象: StoredProcedure [dbo].[Common_Sp_Pagination] 脚本日期: 04/16/2012 21:02:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[Common_Sp_Pagination]
@StrSql NVARCHAR(MAX) ,
@PageIndex INT ,--当前页码
@PageCount INT ,--每页条数
@SortClause NVARCHAR(500) ,--排序字段,不加“Order by”
@Filter NVARCHAR(MAX) , --条件,可以为空,不加where
@TotalNum INT OUTPUT --总记录条数
AS
DECLARE @ExeSql NVARCHAR(MAX)
DECLARE @OrderByStr NVARCHAR(500)
DECLARE @StartRowIndex INT
DECLARE @EndRowIndex INT
SET @StartRowIndex = ( @PageIndex - 1 ) * @PageCount + 1
SET @EndRowIndex = @PageIndex * @PageCount
/*--条件筛选--*/
IF @Filter IS NOT NULL
AND @Filter <> ''
BEGIN
SET @StrSql = @StrSql + ' WHERE 1=1 ' + @Filter
END
/*--排序--*/
IF @SortClause IS NOT NULL
AND @SortClause <> ''
BEGIN
SET @OrderByStr = ' order by ' + @SortClause
SET @ExeSql = ' SELECT * FROM (' + @StrSql
+ ') AS A where rowIndex between ' + CAST(@StartRowIndex AS VARCHAR)
+ ' And ' + CAST(@EndRowIndex AS VARCHAR) + ' ' + @OrderByStr
END
ELSE
BEGIN
SET @ExeSql = ' SELECT * FROM (' + @StrSql
+ ') AS A where rowIndex between ' + CAST(@StartRowIndex AS VARCHAR)
+ ' And ' + CAST(@EndRowIndex AS VARCHAR) + ' '
END
PRINT @StrSql
PRINT @ExeSql
/*---查询总数-*/
CREATE TABLE #temp ( TotalNum INT )
DECLARE @InsertTemp VARCHAR(max)
SET @InsertTemp = 'insert into #temp select count(*) from (' + @StrSql + ') as A'
--PRINT @InsertTemp
EXEC(@InsertTemp)
SELECT @TotalNum = TotalNum
FROM #temp
DROP TABLE #temp
/*--查询分页信息--*/
EXEC(@ExeSql)