CREATEPROC P_viewPage /**//* no_mIss 分页存储过程 2007.2.20 QQ:34813284 适用于单一主键或存在唯一值列的表或视图 */ @TableNameVARCHAR(200), --表名 @FieldListVARCHAR(2000), --显示列名 @PrimaryKeyVARCHAR(100), --单一主键或唯一值键 @WhereVARCHAR(1000), --查询条件 不含'where'字符 @OrderVARCHAR(1000), --排序 不含'order by'字符,如id asc,userid desc,当@SortType=3时生效 @SortTypeINT, --排序规则 1:正序asc 2:倒序desc 3:多列排序 @RecorderCountINT, --记录总数 0:会返回总记录 @PageSizeINT, --每页输出的记录数 @PageIndexINT, --当前页数 @TotalCountINT OUTPUT, --返回记录总数 @TotalPageCountINT OUTPUT --返回总页数 AS SET NOCOUNT ON IFISNULL(@TableName,'') =''ORISNULL(@FieldList,'') ='' ORISNULL(@PrimaryKey,'') ='' OR@SortType<1OR@SortType>3 OR@RecorderCount<0OR@PageSize<0OR@PageIndex<0 BEGIN RETURN END DECLARE@new_where1VARCHAR(1000) DECLARE@new_where2VARCHAR(1000) DECLARE@new_orderVARCHAR(1000) DECLARE@SqlVARCHAR(8000) DECLARE@SqlCountNVARCHAR(4000) IFISNULL(@where,'') ='' BEGIN SET@new_where1='' SET@new_where2=' WHERE ' END ELSE BEGIN SET@new_where1=' WHERE '+@where SET@new_where2=' WHERE '+@where+' AND ' END IFISNULL(@order,'') =''OR@SortType=1OR@SortType=2 BEGIN IF@SortType=1SET@new_order=' ORDER BY '+@PrimaryKey+' ASC' IF@SortType=2SET@new_order=' ORDER BY '+@PrimaryKey+' DESC' END ELSE BEGIN SET@new_order=' ORDER BY '+@Order END SET@SqlCount='SELECT @TotalCount=COUNT(*),@TotalPageCount=CEILING((COUNT(*)+0.0)/' +CAST(@PageSizeASVARCHAR)+') FROM '+@TableName+@new_where1 IF@RecorderCount=0 BEGIN EXEC SP_EXECUTESQL @SqlCount,N'@TotalCount INT OUTPUT,@TotalPageCount INT OUTPUT', @TotalCount OUTPUT,@TotalPageCount OUTPUT END ELSE BEGIN SELECT@TotalCount=@RecorderCount END IF@PageIndex>CEILING((@TotalCount+0.0)/@PageSize) BEGIN SET@PageIndex=CEILING((@TotalCount+0.0)/@PageSize) END IF@PageIndex=1 BEGIN SET@Sql='SELECT TOP '+STR(@PageSize) +''+@FieldList+' FROM ' +@TableName+@new_where1+@new_order END ELSE BEGIN IF@SortType=1 BEGIN SET@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_order+' ) AS TMP) '+@new_order END IF@SortType=2 BEGIN SET@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_order+') AS TMP) '+@new_order END IF@SortType=3 BEGIN IFCHARINDEX(',',@Order) =0BEGINRETURNEND SET@Sql='SELECT TOP '+STR(@PageSize) +''+@FieldList+' FROM ' +@TableName+@new_where2+@PrimaryKey+' NOT IN (SELECT TOP ' +STR(@PageSize*(@PageIndex-1)) +''+@PrimaryKey +' FROM '+@TableName+@new_where1+@new_order+')' +@new_order END END EXEC(@Sql) GO
CREATEPROC P_public_ViewPage /**//* no_mIss 通用分页存储过程 2007.3.1 QQ:34813284 适用于联合主键/单主键/存在能确定唯一行列/存在能确定唯一行的多列 (用英文,隔开) 调用: 第一页查询时返回总记录和总页数及第一页记录: EXECUTE P_public_ViewPage_per 'TableName','col1,col2,col3,col4','pk1,pk2,pk3', 'col5>0 and col7<9','pk1 asc,pk2 asc,pk3 asc',0,10,1, @TotalCount OUTPUT,@TotalPageCount OUTPUT 其它页调用,比如第89页(假设第一页查询时返回总记录为2000000): EXECUTE P_public_ViewPage_per 'TableName','col1,col2,col3,col4','pk1,pk2,pk3', 'col5>0 and col7<9','pk1 asc,pk2 asc,pk3 asc',2000000,10,89, @TotalCount OUTPUT,@TotalPageCount OUTPUT */ @TableNameVARCHAR(200), --表名 @FieldListVARCHAR(2000), --显示列名 @PrimaryKeyVARCHAR(100), --单一主键或唯一值键或联合主键列表(用英文,隔开)或能确定唯一行的多列列表(用英文,隔开) @WhereVARCHAR(1000), --查询条件 不含'where'字符 @OrderVARCHAR(1000), --排序 不含'order by'字符,用英文,隔开 @RecorderCountINT, --记录总数 0:会返回总记录 @PageSizeINT, --每页输出的记录数 @PageIndexINT, --当前页数 @TotalCountINT OUTPUT, --返回记录总数 @TotalPageCountINT OUTPUT --返回总页数 AS SET NOCOUNT ON SET@FieldList=REPLACE(@FieldList,'','') IF@FieldList='*' BEGINSET@FieldList='A.*'END ELSE BEGIN SET@FieldList='A.'+REPLACE(@FieldList,',',',A.') END WHILECHARINDEX(', ',@Order)>0 BEGIN SET@Order=REPLACE(@Order,', ',',') END IFISNULL(@TableName,'') =''ORISNULL(@PrimaryKey,'') ='' OR@RecorderCount<0OR@PageSize<0OR@PageIndex<0 BEGIN RETURN END DECLARE@new_where1VARCHAR(1000) DECLARE@new_where2VARCHAR(1000) DECLARE@new_where3VARCHAR(1000) DECLARE@new_where4VARCHAR(1000) DECLARE@new_order1VARCHAR(1000) DECLARE@new_order2VARCHAR(1000) DECLARE@FieldsVARCHAR(1000) DECLARE@SqlVARCHAR(8000) DECLARE@SqlCountNVARCHAR(4000) SET@Fields=@PrimaryKey+',' SET@new_where2='' SET@new_where4='' IFISNULL(@where,'') ='' BEGIN SET@new_where1='' SET@new_where3=' WHERE ' END ELSE BEGIN SET@new_where1=' WHERE '+@where+'' SET@new_where3=' WHERE 1=1 ' +REPLACE(' AND '+@where,' AND ',' AND A.')+' AND ' END WHILECHARINDEX(',',@Fields)>0 BEGIN SET@new_where2=@new_where2 +'A.'+LTRIM(LEFT(@Fields,CHARINDEX(',',@Fields)-1)) +' = B.'+LTRIM(LEFT(@Fields,CHARINDEX(',',@Fields)-1)) +' AND ' SET@new_where4=@new_where4 +'B.'+LTRIM(LEFT(@Fields,CHARINDEX(',',@Fields)-1)) +' IS NULL AND ' SET@Fields=SUBSTRING(@Fields,CHARINDEX(',',@Fields)+1,LEN(@Fields)) END SET@new_where2=LEFT(@new_where2,LEN(@new_where2)-4) SET@new_where4=LEFT(@new_where4,LEN(@new_where4)-4) IFISNULL(@order,'') ='' BEGIN SET@new_order1='' SET@new_order2='' END ELSE BEGIN SET@new_order1=' ORDER BY '+@Order SET@new_order2=' ORDER BY ' +RIGHT(REPLACE(','+@Order,',',', A.' ), LEN(REPLACE(','+@Order,',',', A.' ))-1) END SET@SqlCount='SELECT @TotalCount=COUNT(*),@TotalPageCount=CEILING((COUNT(*)+0.0)/' +CAST(@PageSizeASVARCHAR)+') FROM '+@TableName +' A '+@new_where1 IF@RecorderCount=0 BEGIN EXEC SP_EXECUTESQL @SqlCount,N'@TotalCount INT OUTPUT,@TotalPageCount INT OUTPUT', @TotalCount OUTPUT,@TotalPageCount OUTPUT END ELSE BEGIN SELECT@TotalCount=@RecorderCount END IF@PageIndex>CEILING((@TotalCount+0.0)/@PageSize) BEGIN SET@PageIndex=CEILING((@TotalCount+0.0)/@PageSize) END IF@PageIndex=1 BEGIN SET@Sql='SELECT TOP '+STR(@PageSize) +''+@FieldList+' FROM ' +@TableName+' A'+@new_where1+@new_order1 END ELSE BEGIN SET@Sql='SELECT TOP '+STR(@PageSize) +''+@FieldList+' FROM ' +@TableName+' A LEFT JOIN (SELECT TOP ' +STR(@PageSize*(@PageIndex-1)) +''+@PrimaryKey+' FROM '+@TableName+@new_where1 +@new_order1+' )B ON '+@new_where2+@new_where3 +@new_where4+@new_order2 END EXEC(@Sql) GO
posted on
2007-10-14 16:50刺猬博客
阅读(371)
评论(0)
编辑收藏举报