CREATEPROCEDURE sp_page @CurrentPageint, @PageSizeint,@Field_Infovarchar(500),@Table_infovarchar(20),@Field_idvarchar(10),@intOrderint,@otherwherevarchar(50),@RecordCountint output,@PageCountint output --@CurrentPage为显示那一页,@PageSize为每一页显示几行,@Field_info为要显示的字段可以为*,@Table_info为要查询的表或视图,@field_id为按这个字段排序,@intorder0为升序排1为降序排,@otherwhere为条件,@RecordCount为总行数,@PageCount为总页数 AS begin DECLARE@MinPageint, @MaxPageint declare@sqlvarchar(1000) declare@sqltnvarchar(300) declare@ordervarchar(4) set@Field_Info=replace(@Field_Info,'''','') --除去@field_info中的' set@Table_info=replace(@table_info,'''','') --除去@table_info中的' set@Field_id=replace(@Field_id,'''','') --除去@field_id中的' set@otherwhere=replace(@otherwhere,'''','''''') --将@otherwhere中的'换成''让SQL语句正确释别' set@sqlt='SELECT @RecordCount = COUNT('+@Field_id+') FROM '+@Table_Info exec sp_executesql @sqlt,N'@RecordCount int output',@RecordCount output --如何将exec执行结果放入变量中,如果是字符串就要用N,N后面的变量一定要和@sqlt里面的变量同名 IF@PageSize<=0 begin set@PageSize=10 end elseif@PageSize>@RecordCount begin set@pageSize=@RecordCount end set@pagecount=@RecordCount/@PageSize if ((@recordcount%@pagesize) !=0) --如果除不尽则加一页 begin set@PageCount=@RecordCount/@PageSize set@PageCount=@pagecount+1 end else begin set@pagecount=@recordcount/@PageSize end IF@CurrentPage<=0 begin set@CurrentPage=1 end elseif@CurrentPage>@pagecount begin set@currentpage=@pagecount--如果输入页数大于总页数则符最后一页 end SET@MinPage= (@CurrentPage-1) *@PageSize+1 SET@MaxPage=@MinPage+@PageSize-1 BEGIN if@intorder=0 set@order='asc' else set@order='desc' if@Field_Infolike'' set@field_Info='*' if@otherwherelike'' set@sql='select '+@Field_Info+' from (select '+@Field_Info+' , row_number() over(order by '+@Field_id+''+@Order+') as rownumber from '+@Table_info+') as a where rownumber between '+convert(varchar(10),@minpage) +' and '+convert(varchar(10),@maxpage) else set@sql='select '+@Field_Info+' from (select '+@Field_Info+' , row_number() over(order by '+@Field_id+''+@Order+') as rownumber from '+@Table_info+') as a where rownumber between '+convert(varchar(10),@minpage) +' and '+convert(varchar(10),@maxpage) +' and '+@otherwhere exec(@sql) END end declare@rconint declare@pconint exec sp_page 8,73,'','user_info','id',0,'',@rcon output,@pcon output