CREATE PROCEDURE UP_GetRecordByPageTest
(
@tblName VarChar(255), --表名
@fldName VarChar(255), --主键
@PageIndex int, --当前页数
@PageSize int, --每页记录数
@strOrder nvarchar(1000), --排序条件(不需要order by,需要asc和desc字符)
@strWhere nvarchar(1000)='1=1', --查询条件
@ReCount int out ----输出总数量
)
AS
begin
declare @topCount int--排除数据量
set @topCount =(@PageIndex-1)*@PageSize --
end
begin
select top @PageSize* from @tblName where @fldName not in (select top @topCount @fldName from @tblName order by @strOrder)
and @strWhere order by @strOrder
end
begin
select @ReCount=count(*) from @tblName where @strWhere
end
GO
(
@tblName VarChar(255), --表名
@fldName VarChar(255), --主键
@PageIndex int, --当前页数
@PageSize int, --每页记录数
@strOrder nvarchar(1000), --排序条件(不需要order by,需要asc和desc字符)
@strWhere nvarchar(1000)='1=1', --查询条件
@ReCount int out ----输出总数量
)
AS
begin
declare @topCount int--排除数据量
set @topCount =(@PageIndex-1)*@PageSize --
end
begin
select top @PageSize* from @tblName where @fldName not in (select top @topCount @fldName from @tblName order by @strOrder)
and @strWhere order by @strOrder
end
begin
select @ReCount=count(*) from @tblName where @strWhere
end
GO
(
@tblName VarChar(255), --表名
@fldName VarChar(255), --主键
@PageIndex int, --当前页数
@PageSize int, --每页记录数
@strOrder nvarchar(1000), --排序条件(不需要order by,需要asc和desc字符)
@strWhere nvarchar(1000)='1=1', --查询条件
@ReCount int out ----输出总数量
)
AS
begin
declare @sqlString nvarchar(2000) --最终Sql语句
declare @topCount int--排除数据量set @topCount =(@PageIndex-1)*@PageSize
--
set @sqlString='select top '+ cast(@PageSize as varchar)+' * from '+ @tblName +' where
'+@fldName+' not in (select top '+cast(@topCount as varchar)+' '+ @fldName +' from '+ @tblName+' order by '+@strOrder+') and '+@strWhere+'
order by '+@strOrder
end
begin
declare @returnCount nvarchar(1000)
set @returnCount='select @Re=count(*) from '+ @tblName + ' where '+ @strWhere
--declare curRecordCount cursor for @returnCount select count(*) as RecordCount from @tblName where @strWhereend
--print @sqlString--打印存储过程
--exec ('select ReCount =cast(count(*) as varchar) from '+ @tblName + ' where '+ @strWhere)
exec sp_executesql @returnCount ,N'@Re int output',@ReCount output
--print cast(@ReCount as varchar)
exec (@sqlString)
GO