通用分页存过程

CREATE PROCEDURE [dbo].[USP_GetFrontDataList_ByFullIndex] ( @TableViewQueryName　Varchar( 1024 ),　 --Table或View或者Query的名字或字符串 @Key　　Varchar( 50 ),　--关键字 @SelectStr　 Varchar( 500 ),　 --选择列字符串 @Criteria　 Varchar( 8000 ),--查询条件 @Sort　　Varchar( 255 ), --排序字符串 @FristTopNum　 INT,　　　--显示的第一页置顶的数目 @Page　　BIGINT　OUTPUT, --显示的当前页号 @CurrentPageRow　BIGINT,　--页大小(显示多少行) @TotalCount　 BIGINT OUTPUT　 --通过该查询条件，返回的查询记录的总页数 ) ASSET NOCOUNT ONif charindex(';',@Criteria)>0 or charindex('--',@Criteria)>0 or charindex('/*',@Criteria)>0 or charindex('*/',@Criteria)>0 or charindex('syscolumns',@Criteria)>0　orcharindex('sysfiles',@Criteria)>0 or charindex('char(124)',@Criteria)>0　or charindex('1=1',@Criteria)>0 RETURN DECLARE @TotalStr　nVarchar(4000) DECLARE @Str　 nVarchar(4000) DECLARE @TopRowNum bigint IF　@SelectStr IS NULL AND RTRIM(LTRIM(@Criteria)) = '' SET @SelectStr = '*' IF @FristTopNum IS NULL AND @FristTopNum < 0 BEGIN SET @FristTopNum = 0 END ELSE IF @FristTopNum > @CurrentPageRow BEGIN SET @FristTopNum = @CurrentPageRow ENDIF　@CurrentPageRow > 0 BEGIN IF @Criteria IS NOT NULL AND RTRIM(LTRIM(@Criteria)) <> '' BEGIN SET　@TotalStr = 'SELECT @TotalCount=COUNT(*) FROM　' +'('+ @TableViewQueryName　 +')'+ ' T ' + ' WHERE ' + @Criteria END ELSE BEGIN SET　@TotalStr = 'SELECT @TotalCount=COUNT(*) FROM ' +'('+@TableViewQueryName　+')'+ ' T ' END PRINT @TotalStr EXEC　sp_ExecuteSql @TotalStr, N'@TotalCount bigint output',@TotalCount output SET @TotalCount = @TotalCount + isnull(@FristTopNum　,0) DECLARE @TotalPage bigint SET @TotalPage = @TotalCount/@CurrentPageRow IF @TotalCount%@CurrentPageRow > 0 BEGIN SET @TotalPage = @TotalPage + 1 END IF @Page <= 0 BEGIN SET @Page = 1 END IF @TotalPage > 0 AND @Page > @TotalPage BEGIN SET @Page = @TotalPage END--组织查询语句 SET　@Str =　'SELECT　 ' +　 @SelectStr + ' FROM (' +　@TableViewQueryName　+ ') T WHERE　T.SerialNumber >' + cast ((@Page-1)　as varchar(10)) + '*' +cast( @CurrentPageRow as varchar(10))+　' AND T.SerialNumber <= '+ cast (@Page　as varchar(10)) + '*' +cast( @CurrentPageRow as varchar(10))IF @Sort IS NOT NULL ANDRTRIM(LTRIM(@Sort)) <> '' BEGIN IF @Criteria IS NOT NULL AND　RTRIM(LTRIM(@Criteria)) <> '' BEGIN SET　@Str = @Str +　 ' AND (' + @Criteria + ') ORDER BY '+@Sort END ELSE BEGIN SET　@Str = @Str +　 ' AND　(' + @Criteria + ') ORDER BY '+@Sort END END ELSE BEGIN IF @Criteria IS NOT NULL AND　 RTRIM(LTRIM(@Criteria)) <> '' BEGIN SET @str = @str +　' AND (' + @Criteria + ')　'　 END END --对无记录时当前页数的处理 IF @TotalCount=0 BEGIN SET @Page = 0 END END EXEC　sp_ExecuteSql @Str