set ANSI_NULLS ONset QUOTED_IDENTIFIER ONgo-- 获取指定页的数据，可以为返回字段重新命名别名ALTER PROCEDURE [dbo].[ExpGetRecordFromPageByotherFieldsName] @TableName VARCHAR(4000), --表名 @FieldList VARCHAR(4000), --显示列名，如果是全部字段则为* @PrimaryKey VARCHAR(500), --单一主键或唯一值键 @Where VARCHAR(5000), --查询条件 不含'where'字符，如id>10 and len(userid)>9 @Order VARCHAR(4000), --排序 不含'order by'字符，如id asc,userid desc，必须指定asc或desc --注意当@SortType=3时生效，记住一定要在最后加上主键，否则会让你比较郁闷 @SortType INT, --排序规则 1:正序asc 2:倒序desc 3:多列排序方法 @RecorderCount INT, --记录总数 0:会返回总记录 @PageSize INT, --每页输出的记录数 @PageIndex INT, --当前页数 @oFIELDS VARCHAR(1024), --别名字段 @TotalCount INT OUTPUT, --记返回总记录 @TotalPageCount INT OUTPUT --返回总页数AS SET NOCOUNT ONIF LEN(@oFIELDS)>0 --有别名字段BEGINIF ISNULL(@TotalCount,'') = '' SET @TotalCount = 0SET @Order = RTRIM(LTRIM(@Order))SET @PrimaryKey = RTRIM(LTRIM(@PrimaryKey))-- SET @FieldList = REPLACE(RTRIM(LTRIM(@FieldList)),' ','')--于2010-09-12注释：要求为字段加别名不能将中间空格去掉 SET @FieldList = RTRIM(LTRIM(@FieldList))WHILE CHARINDEX(', ',@Order) > 0 OR CHARINDEX(' ,',@Order) > 0BEGINSET @Order = REPLACE(@Order,', ',',')SET @Order = REPLACE(@Order,' ,',',') ENDIF ISNULL(@TableName,'') = '' OR ISNULL(@FieldList,'') = '' OR ISNULL(@PrimaryKey,'') = ''OR @SortType < 1 OR @SortType >3OR @RecorderCount < 0 OR @PageSize < 0 OR @PageIndex < 0 BEGIN PRINT('ERR_00') RETURNEND IF @SortType = 3BEGINIF (UPPER(RIGHT(@Order,4))!=' ASC' AND UPPER(RIGHT(@Order,5))!=' DESC')BEGIN PRINT('ERR_02') RETURN ENDENDDECLARE @new_where1 VARCHAR(1000)DECLARE @new_where2 VARCHAR(1000)DECLARE @new_order1 VARCHAR(1000) DECLARE @new_order2 VARCHAR(1000)DECLARE @new_order3 VARCHAR(1000)DECLARE @Sql VARCHAR(8000)DECLARE @SqlCount NVARCHAR(4000)IF ISNULL(@where,'') = ''BEGINSET @new_where1 = ' 'SET @new_where2 = ' WHERE 'ENDELSEBEGINSET @new_where1 = ' WHERE ' + @where SET @new_where2 = ' WHERE ' + @where + ' AND 'ENDIF ISNULL(@order,'') = '' OR @SortType = 1 OR @SortType = 2 BEGINIF @SortType = 1 BEGIN SET @new_order1 = ' ORDER BY ' + @PrimaryKey + ' ASC'SET @new_order2 = ' ORDER BY ' + @PrimaryKey + ' DESC'ENDIF @SortType = 2 BEGIN SET @new_order1 = ' ORDER BY ' + @PrimaryKey + ' DESC'SET @new_order2 = ' ORDER BY ' + @PrimaryKey + ' ASC'ENDENDELSEBEGINSET @new_order1 = ' ORDER BY ' + @OrderENDIF @SortType = 3 AND CHARINDEX(','+@PrimaryKey+' ',','+@Order)>0BEGINSET @new_order1 = ' ORDER BY ' + @OrderSET @new_order2 = @Order + ',' SET @new_order2 = REPLACE(REPLACE(@new_order2,'ASC,','{ASC},'),'DESC,','{DESC},') SET @new_order2 = REPLACE(REPLACE(@new_order2,'{ASC},','DESC,'),'{DESC},','ASC,')SET @new_order2 = ' ORDER BY ' + SUBSTRING(@new_order2,1,LEN(@new_order2)-1) IF @FieldList <> '*'BEGIN SET @new_order3 = REPLACE(REPLACE(@Order + ',','ASC,',','),'DESC,',',') SET @FieldList = ',' + @FieldList WHILE CHARINDEX(',',@new_order3)>0BEGINIF CHARINDEX(SUBSTRING(','+@new_order3,1,CHARINDEX(',',@new_order3)),','+@FieldList+',')>0BEGIN SET @FieldList = @FieldList + ',' + SUBSTRING(@new_order3,1,CHARINDEX(',',@new_order3)) ENDSET @new_order3 = SUBSTRING(@new_order3,CHARINDEX(',',@new_order3)+1,LEN(@new_order3))ENDSET @FieldList = SUBSTRING(@FieldList,2,LEN(@FieldList)) END ENDSET @SqlCount = 'SELECT @TotalCount=COUNT(*),@TotalPageCount=CEILING((COUNT(*)+0.0)/'+ CAST(@PageSize AS VARCHAR)+') FROM ' + @TableName + @new_where1 IF @RecorderCount = 0BEGIN EXEC SP_EXECUTESQL @SqlCount,N'@TotalCount INT OUTPUT,@TotalPageCount INT OUTPUT', @TotalCount OUTPUT,@TotalPageCount OUTPUTENDELSEBEGIN SELECT @TotalCount = @RecorderCount ENDIF @PageIndex > CEILING((@TotalCount+0.0)/@PageSize)BEGINSET @PageIndex = CEILING((@TotalCount+0.0)/@PageSize)ENDIF @PageIndex = 1 OR @PageIndex >= CEILING((@TotalCount+0.0)/@PageSize)BEGIN IF @PageIndex = 1 --返回第一页数据BEGINSET @Sql = 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ' + @TableName + @new_where1 + @new_order1 ENDIF @PageIndex <= CEILING((@TotalCount+0.0)/@PageSize) --返回最后一页数据BEGINSET @Sql = 'SELECT TOP ' + STR(@PageSize) + ' ' + @oFIELDS + ' FROM (' + 'SELECT TOP ' + STR(ABS(@PageSize*@PageIndex-@TotalCount-@PageSize)) + ' ' + @FieldList + ' FROM ' + @TableName + @new_where1 + @new_order2 + ' ) AS TMP ' + @new_order1 END END ELSEBEGINIF @SortType = 1 --仅主键正序排序BEGINIF @PageIndex <= CEILING((@TotalCount+0.0)/@PageSize)/2 --正向检索BEGINSET @Sql = 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ' + @TableName + @new_where2 + @PrimaryKey + ' > ' + '(SELECT MAX(' + @PrimaryKey + ') FROM (SELECT TOP ' + STR(@PageSize*(@PageIndex-1)) + ' ' + @PrimaryKey + ' FROM ' + @TableName + @new_where1 + @new_order1 +' ) AS TMP) '+ @new_order1ENDELSE --反向检索BEGINSET @Sql = 'SELECT TOP ' + STR(@PageSize) + ' ' + @oFIELDS + ' FROM (' + 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ' + @TableName + @new_where2 + @PrimaryKey + ' < ' + '(SELECT MIN(' + @PrimaryKey + ') FROM (SELECT TOP ' + STR(@TotalCount-@PageSize*@PageIndex) + ' ' + @PrimaryKey + ' FROM ' + @TableName + @new_where1 + @new_order2 +' ) AS TMP) '+ @new_order2 + ' ) AS TMP ' + @new_order1ENDENDIF @SortType = 2 --仅主键反序排序BEGINIF @PageIndex <= CEILING((@TotalCount+0.0)/@PageSize)/2 --正向检索BEGINSET @Sql = 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ' + @TableName + @new_where2 + @PrimaryKey + ' < ' + '(SELECT MIN(' + @PrimaryKey + ') FROM (SELECT TOP ' + STR(@PageSize*(@PageIndex-1)) + ' ' + @PrimaryKey +' FROM '+ @TableName + @new_where1 + @new_order1 + ') AS TMP) '+ @new_order1 END ELSE --反向检索BEGINSET @Sql = 'SELECT TOP ' + STR(@PageSize) + ' ' + @oFIELDS + ' FROM (' + 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ' + @TableName + @new_where2 + @PrimaryKey + ' > ' + '(SELECT MAX(' + @PrimaryKey + ') FROM (SELECT TOP ' + STR(@TotalCount-@PageSize*@PageIndex) + ' ' + @PrimaryKey + ' FROM ' + @TableName + @new_where1 + @new_order2 +' ) AS TMP) '+ @new_order2 + ' ) AS TMP ' + @new_order1END END IF @SortType = 3 --多列排序，必须包含主键，且放置最后，否则不处理BEGINIF CHARINDEX(',' + @PrimaryKey + ' ',',' + @Order) = 0 BEGIN PRINT('ERR_02') RETURN ENDIF @PageIndex <= CEILING((@TotalCount+0.0)/@PageSize)/2 --正向检索BEGINSET @Sql = 'SELECT TOP ' + STR(@PageSize) + ' ' + @oFIELDS + ' FROM ( ' + 'SELECT TOP ' + STR(@PageSize) + ' ' + @oFIELDS + ' FROM ( ' + ' SELECT TOP ' + STR(@PageSize*@PageIndex) + ' ' + @FieldList + ' FROM ' + @TableName + @new_where1 + @new_order1 + ' ) AS TMP ' + @new_order2 + ' ) AS TMP ' + @new_order1 ENDELSE --反向检索BEGINSET @Sql = 'SELECT TOP ' + STR(@PageSize) + ' ' + @oFIELDS + ' FROM ( ' + 'SELECT TOP ' + STR(@PageSize) + ' ' + @oFIELDS + ' FROM ( ' + ' SELECT TOP ' + STR(@TotalCount-@PageSize*@PageIndex+@PageSize) + ' ' + @FieldList + ' FROM ' + @TableName + @new_where1 + @new_order2 + ' ) AS TMP ' + @new_order1 + ' ) AS TMP ' + @new_order1ENDENDENDPRINT(@Sql)EXEC(@Sql)ENDELSE--没有别名字段BEGINIF ISNULL(@TotalCount,'') = '' SET @TotalCount = 0SET @Order = RTRIM(LTRIM(@Order))SET @PrimaryKey = RTRIM(LTRIM(@PrimaryKey))-- SET @FieldList = REPLACE(RTRIM(LTRIM(@FieldList)),' ','')--于2010-09-12注释：要求为字段加别名不能将中间空格去掉 SET @FieldList = RTRIM(LTRIM(@FieldList))WHILE CHARINDEX(', ',@Order) > 0 OR CHARINDEX(' ,',@Order) > 0BEGINSET @Order = REPLACE(@Order,', ',',')SET @Order = REPLACE(@Order,' ,',',') ENDIF ISNULL(@TableName,'') = '' OR ISNULL(@FieldList,'') = '' OR ISNULL(@PrimaryKey,'') = ''OR @SortType < 1 OR @SortType >3OR @RecorderCount < 0 OR @PageSize < 0 OR @PageIndex < 0 BEGIN PRINT('ERR_00') RETURNEND IF @SortType = 3BEGINIF (UPPER(RIGHT(@Order,4))!=' ASC' AND UPPER(RIGHT(@Order,5))!=' DESC')BEGIN PRINT('ERR_02') RETURN ENDENDDECLARE @new_where1_1 VARCHAR(1000)DECLARE @new_where2_1 VARCHAR(1000)DECLARE @new_order1_1 VARCHAR(1000) DECLARE @new_order2_1 VARCHAR(1000)DECLARE @new_order3_1 VARCHAR(1000)DECLARE @Sql_1 VARCHAR(8000)DECLARE @Sql_1Count NVARCHAR(4000)IF ISNULL(@where,'') = ''BEGINSET @new_where1_1 = ' 'SET @new_where2_1 = ' WHERE 'ENDELSEBEGINSET @new_where1_1 = ' WHERE ' + @where SET @new_where2_1 = ' WHERE ' + @where + ' AND 'ENDIF ISNULL(@order,'') = '' OR @SortType = 1 OR @SortType = 2 BEGINIF @SortType = 1 BEGIN SET @new_order1_1 = ' ORDER BY ' + @PrimaryKey + ' ASC'SET @new_order2_1 = ' ORDER BY ' + @PrimaryKey + ' DESC'ENDIF @SortType = 2 BEGIN SET @new_order1_1 = ' ORDER BY ' + @PrimaryKey + ' DESC'SET @new_order2_1 = ' ORDER BY ' + @PrimaryKey + ' ASC'ENDENDELSEBEGINSET @new_order1_1 = ' ORDER BY ' + @OrderENDIF @SortType = 3 AND CHARINDEX(','+@PrimaryKey+' ',','+@Order)>0BEGINSET @new_order1_1 = ' ORDER BY ' + @OrderSET @new_order2_1 = @Order + ',' SET @new_order2_1 = REPLACE(REPLACE(@new_order2_1,'ASC,','{ASC},'),'DESC,','{DESC},') SET @new_order2_1 = REPLACE(REPLACE(@new_order2_1,'{ASC},','DESC,'),'{DESC},','ASC,')SET @new_order2_1 = ' ORDER BY ' + SUBSTRING(@new_order2_1,1,LEN(@new_order2_1)-1) IF @FieldList <> '*'BEGIN SET @new_order3_1 = REPLACE(REPLACE(@Order + ',','ASC,',','),'DESC,',',') SET @FieldList = ',' + @FieldList WHILE CHARINDEX(',',@new_order3_1)>0BEGINIF CHARINDEX(SUBSTRING(','+@new_order3_1,1,CHARINDEX(',',@new_order3_1)),','+@FieldList+',')>0BEGIN SET @FieldList = @FieldList + ',' + SUBSTRING(@new_order3_1,1,CHARINDEX(',',@new_order3_1)) ENDSET @new_order3_1 = SUBSTRING(@new_order3_1,CHARINDEX(',',@new_order3_1)+1,LEN(@new_order3_1))ENDSET @FieldList = SUBSTRING(@FieldList,2,LEN(@FieldList)) END ENDSET @Sql_1Count = 'SELECT @TotalCount=COUNT(*),@TotalPageCount=CEILING((COUNT(*)+0.0)/'+ CAST(@PageSize AS VARCHAR)+') FROM ' + @TableName + @new_where1_1 IF @RecorderCount = 0BEGIN EXEC SP_EXECUTESQL @Sql_1Count,N'@TotalCount INT OUTPUT,@TotalPageCount INT OUTPUT', @TotalCount OUTPUT,@TotalPageCount OUTPUTENDELSEBEGIN SELECT @TotalCount = @RecorderCount ENDIF @PageIndex > CEILING((@TotalCount+0.0)/@PageSize)BEGINSET @PageIndex = CEILING((@TotalCount+0.0)/@PageSize)ENDIF @PageIndex = 1 OR @PageIndex >= CEILING((@TotalCount+0.0)/@PageSize)BEGINIF @PageIndex = 1 --返回第一页数据BEGINSET @Sql_1 = 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ' + @TableName + @new_where1_1 + @new_order1_1ENDIF @PageIndex >= CEILING((@TotalCount+0.0)/@PageSize) --返回最后一页数据BEGINSET @Sql_1 = 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM (' + 'SELECT TOP ' + STR(ABS(@PageSize*@PageIndex-@TotalCount-@PageSize)) + ' ' + @FieldList + ' FROM ' + @TableName + @new_where1_1 + @new_order2_1 + ' ) AS TMP ' + @new_order1_1 END END ELSEBEGINIF @SortType = 1 --仅主键正序排序BEGINIF @PageIndex <= CEILING((@TotalCount+0.0)/@PageSize)/2 --正向检索BEGINSET @Sql_1 = 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ' + @TableName + @new_where2_1 + @PrimaryKey + ' > ' + '(SELECT MAX(' + @PrimaryKey + ') FROM (SELECT TOP ' + STR(@PageSize*(@PageIndex-1)) + ' ' + @PrimaryKey + ' FROM ' + @TableName + @new_where1_1 + @new_order1_1 +' ) AS TMP) '+ @new_order1_1ENDELSE --反向检索BEGINSET @Sql_1 = 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM (' + 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ' + @TableName + @new_where2_1 + @PrimaryKey + ' < ' + '(SELECT MIN(' + @PrimaryKey + ') FROM (SELECT TOP ' + STR(@TotalCount-@PageSize*@PageIndex) + ' ' + @PrimaryKey + ' FROM ' + @TableName + @new_where1_1 + @new_order2_1 +' ) AS TMP) '+ @new_order2_1 + ' ) AS TMP ' + @new_order1_1ENDENDIF @SortType = 2 --仅主键反序排序BEGINIF @PageIndex <= CEILING((@TotalCount+0.0)/@PageSize)/2 --正向检索BEGINSET @Sql_1 = 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ' + @TableName + @new_where2_1 + @PrimaryKey + ' < ' + '(SELECT MIN(' + @PrimaryKey + ') FROM (SELECT TOP ' + STR(@PageSize*(@PageIndex-1)) + ' ' + @PrimaryKey +' FROM '+ @TableName + @new_where1_1 + @new_order1_1 + ') AS TMP) '+ @new_order1_1 END ELSE --反向检索BEGINSET @Sql_1 = 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM (' + 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ' + @TableName + @new_where2_1 + @PrimaryKey + ' > ' + '(SELECT MAX(' + @PrimaryKey + ') FROM (SELECT TOP ' + STR(@TotalCount-@PageSize*@PageIndex) + ' ' + @PrimaryKey + ' FROM ' + @TableName + @new_where1_1 + @new_order2_1 +' ) AS TMP) '+ @new_order2_1 + ' ) AS TMP ' + @new_order1_1END END IF @SortType = 3 --多列排序，必须包含主键，且放置最后，否则不处理BEGINIF CHARINDEX(',' + @PrimaryKey + ' ',',' + @Order) = 0 BEGIN PRINT('ERR_02') RETURN ENDIF @PageIndex <= CEILING((@TotalCount+0.0)/@PageSize)/2 --正向检索BEGINSET @Sql_1 = 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ( ' + 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ( ' + ' SELECT TOP ' + STR(@PageSize*@PageIndex) + ' ' + @FieldList + ' FROM ' + @TableName + @new_where1_1 + @new_order1_1 + ' ) AS TMP ' + @new_order2_1 + ' ) AS TMP ' + @new_order1_1 ENDELSE --反向检索BEGINSET @Sql_1 = 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ( ' + 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ( ' + ' SELECT TOP ' + STR(@TotalCount-@PageSize*@PageIndex+@PageSize) + ' ' + @FieldList + ' FROM ' + @TableName + @new_where1_1 + @new_order2_1 + ' ) AS TMP ' + @new_order1_1 + ' ) AS TMP ' + @new_order1_1ENDENDENDPRINT(@Sql_1)EXEC(@Sql_1)END
