ALTER PROCEDURE [dbo].[p_Paging_RowCount]
(
@TableName varchar(100), --表名
@Fields varchar(5000) , --字段名(全部字段为*)
@OrderField varchar(5000), --排序字段(必须!支持多字段)
@sqlWhere varchar(5000),--条件语句(不用加where)
@pageSize int, --每页多少条记录
@pageIndex int , --指定当前为第几页
@TotalPage int output, --返回总页数
@TotalRecord int output -- 返回总记录数
)
AS
begin
Begin Tran --开始事务
Declare @sql nvarchar(4000);
--Declare @totalRecord int;
--计算总记录数
if (@SqlWhere='' or @sqlWhere is NULL)
begin
set @sql = 'select @TotalRecord = count(*) from ' + @TableName
end
else
begin
if(@SqlWhere like '%group by%')
set @sql = 'select @TotalRecord = count(*) from (select a.ID,a.MingCheng,a.ShangYeQuan,a.XingXiangPic,a.FangJianNum,a.ZhuCeTime,a.Dizhi,a.DianHua,a.Jiaotong,a.Xingji,a.Remark,b.MID from ' + @TableName + ' where ' + @sqlWhere+') as A'
else
set @sql = 'select @TotalRecord = count(*) from ' + @TableName + ' where ' + @sqlWhere
end
EXEC sp_executesql @sql,N'@TotalRecord int OUTPUT',@TotalRecord OUTPUT--计算总记录数
--计算总页数
select @TotalPage=CEILING((@TotalRecord+0.0)/@PageSize)
if (@SqlWhere='' or @sqlWhere is NULL)
set @sql = 'Select * FROM (select ROW_NUMBER() Over(order by ' + @OrderField + ') as rowId,' + @Fields + ' from ' + @TableName
else
set @sql = 'Select * FROM (select ROW_NUMBER() Over(order by ' + @OrderField + ') as rowId,' + @Fields + ' from ' + @TableName + ' where ' + @SqlWhere
--处理页数超出范围情况
if @PageIndex<=0
Set @pageIndex = 1
if @pageIndex>@TotalPage
Set @pageIndex = @TotalPage
--处理开始点和结束点
Declare @StartRecord int
Declare @EndRecord int
set @StartRecord = (@pageIndex-1)*@PageSize + 1
set @EndRecord = @StartRecord + @pageSize - 1
--继续合成sql语句
set @Sql = @Sql + ') as myTable where rowId between ' + Convert(varchar(50),@StartRecord) + ' and ' + Convert(varchar(50),@EndRecord)
Exec(@Sql)
--select @sql
---------------------------------------------------
If @@Error <> 0
Begin
RollBack Tran
Return -1
End
Else
Begin
Commit Tran
--Return @totalRecord ---返回记录总数
End
end
------解决方案--------------------
帮你修改还不如直接给你一个现成可用的.参考吧
CREATE PROC proc_pageview
@tbname sysname, --要分页显示的表名
@FieldKey nvarchar(1000), --用于定位记录的主键(惟一键)字段,可以是逗号分隔的多个字段
@PageCurrent int=1, --要显示的页码
@PageSize int=10, --每页的大小(记录数)
@FieldShow nvarchar(1000)='', --以逗号分隔的要显示的字段列表,如果不指定,则显示所有字段
@FieldOrder nvarchar(1000)='', --以逗号分隔的排序字段列表,可以指定在字段后面指定DESC/ASC
@Where varchar(1000)='', --查询条件
@RecordCount int OUTPUT --总页数
AS
SET NOCOUNT ON
--检查对象是否有效
IF OBJECT_ID(@tbname) IS NULL
BEGIN
RAISERROR(N'对象"%s"不存在',1,16,@tbname)
RETURN
END
IF OBJECTPROPERTY(OBJECT_ID(@tbname),N'IsTable')=0
AND OBJECTPROPERTY(OBJECT_ID(@tbname),N'IsView')=0
AND OBJECTPROPERTY(OBJECT_ID(@tbname),N'IsTableFunction')=0
BEGIN
RAISERROR(N'"%s"不是表、视图或者表值函数',1,16,@tbname)
RETURN