CREATE Procedure prGetRecordByPage
(
@PageSize int, --每页的记录条数
@PageNumber int, --当前页面
@QuerySql varchar(100),--部分查询字符串,如* From Test order by id desc
@KeyField varchar(500),@pageCount int output,
@recordCount int output
)
AS
Beginselect @recordCount='count(*) '+(@QuerySql)set @pageCount=ceiling(@recordCount*1.0/@PageSize )
Declare @SqlTable AS varchar(1000)
Declare @SqlText AS Varchar(1000)
Set @SqlTable='Select Top '+CAST(@PageNumber*@PageSize AS varchar(30))+'* '+@QuerySql
Set @SqlText='Select Top '+Cast(@PageSize AS varchar(30))+' * From '
+'('+@SqlTable+') As TembTbA '
+'Where '+@KeyField+' Not In (Select Top '+CAST((@PageNumber-1)*@PageSize AS varchar(30))+' '+@KeyField+' From '
+'('+@SqlTable+') AS TempTbB)'
Exec(@SqlText)EndGO
---------------------------------------------------
....//得到页数和记录数
sqlCommand1.Parameters.Add("@pageCount",SqlDbType.Int);
sqlCommand1.Parameters["@pageCount"].Value = pageCount;
sqlCommand1.Parameters["@pageCount"].Direction = ParameterDirection.Output; //存储过程中的输出参数 sqlCommand1.Parameters.Add("@recordCount",SqlDbType.Int); sqlCommand1.Parameters["@recordCount"].Value = recordCount; sqlCommand1.Parameters["@recordCount"].Direction = ParameterDirection.Output; //存储过程中的输出参数
....
/////////
(
@PageSize int, --每页的记录条数
@PageNumber int, --当前页面
@QuerySql varchar(100),--部分查询字符串,如* From Test order by id desc
@KeyField varchar(500),@pageCount int output,
@recordCount int output
)
AS
Beginselect @recordCount='count(*) '+(@QuerySql)set @pageCount=ceiling(@recordCount*1.0/@PageSize )
Declare @SqlTable AS varchar(1000)
Declare @SqlText AS Varchar(1000)
Set @SqlTable='Select Top '+CAST(@PageNumber*@PageSize AS varchar(30))+'* '+@QuerySql
Set @SqlText='Select Top '+Cast(@PageSize AS varchar(30))+' * From '
+'('+@SqlTable+') As TembTbA '
+'Where '+@KeyField+' Not In (Select Top '+CAST((@PageNumber-1)*@PageSize AS varchar(30))+' '+@KeyField+' From '
+'('+@SqlTable+') AS TempTbB)'
Exec(@SqlText)EndGO
---------------------------------------------------
....//得到页数和记录数
sqlCommand1.Parameters.Add("@pageCount",SqlDbType.Int);
sqlCommand1.Parameters["@pageCount"].Value = pageCount;
sqlCommand1.Parameters["@pageCount"].Direction = ParameterDirection.Output; //存储过程中的输出参数 sqlCommand1.Parameters.Add("@recordCount",SqlDbType.Int); sqlCommand1.Parameters["@recordCount"].Value = recordCount; sqlCommand1.Parameters["@recordCount"].Direction = ParameterDirection.Output; //存储过程中的输出参数
....
/////////
解决方案 »
- MVC多层架构的ADO.net数据的读取问题
- 求个两页面传值的js 要求至少能传5KB 有经验的请进
- FreeTextBox用ftb.imagegallery.aspx上传图片,怎么样同时将图片名改为yymmddhhmmss.jpg型式
- installutil.exe 在哪下载?
- 我想我的这个问题对于刚毕业的人来说都会碰到,欢迎各位指点,迷茫
- 一个简单的问题,我调试的时候老是出错,快要疯了,请各位大侠帮帮忙指点指点
- 简单问题,随机数问题
- DataList更新数据库问题
- 如何将TimeSpan转化成月
- 如何让datagrid有上下滚动条?
- 母版页上FindControl问题
- 关于datareader的异常处理问题,高手请进。
create table aaa(id int)
insert into aaa select 1
declare @sqlstr nvarchar(1000)
declare @count int
select @Sqlstr='select @count=count(1) from aaa'exec sp_executesql @sqlstr,N'@count int output',@count outputselect @countdrop table aaa