CREATEPROC dt_Pagination /**//* 适用于联合主键/单主键/存在能确定唯一行列/存在能确定唯一行的多列 (用英文,隔开) 调用: 第一页查询时返回总记录和总页数及第一页记录: 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, @RecordCount OUTPUT,@PageCount 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, @RecordCount OUTPUT,@PageCount OUTPUT */ @TableNameVARCHAR(200), --表名 @FieldListVARCHAR(2000), --显示列名 @PrimaryKeyVARCHAR(100), --单一主键或唯一值键或联合主键列表(用英文,隔开)或能确定唯一行的多列列表(用英文,隔开) @WhereVARCHAR(1000), --查询条件 不含'where'字符 @OrderVARCHAR(1000), --排序 不含'order by'字符,用英文,隔开 @RecorderCountINT, --记录总数 0:会返回总记录 @PageSizeINT, --每页输出的记录数 @PageIndexINT, --当前页数 @PageCountINT OUTPUT, --返回总页数 @RecordCountINT 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 @RecordCount=COUNT(*),@PageCount=CEILING((COUNT(*)+0.0)/' +CAST(@PageSizeASVARCHAR)+') FROM '+@TableName +' A '+@new_where1 IF@RecorderCount=0 BEGIN EXEC SP_EXECUTESQL @SqlCount,N'@RecordCount INT OUTPUT,@PageCount INT OUTPUT', @RecordCount OUTPUT,@PageCount OUTPUT END ELSE BEGIN SELECT@RecordCount=@RecorderCount END IF@PageIndex>CEILING((@RecordCount+0.0)/@PageSize) BEGIN SET@PageIndex=CEILING((@RecordCount+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