呼唤vivianfdlpw

[tings]

见：http://community.csdn.net/Expert/topic/4359/4359826.xml?temp=.686352
CREATE PROCEDURE [GetCustomersDataPage]
@PageIndex INT, --以1开始
@PageSize INT,
@strUserName VARCHAR(30),
@RecordCount INT OUT,
@PageCount INT OUT

AS
SELECT @RecordCount=COUNT(*) FROM MoneyRecord WHERE UserName=@strUserName
SET @PageCount = CEILING(@RecordCount * 1.0 / @PageSize)

DECLARE @SQLSTR VARCHAR(500)

if @PageIndex<=0
set @PageIndex=1

SET @SQLSTR='select top '+convert(varchar,@PageSize)
+' RecordID,myDateTime,MoneyType,MoneyNum,myKeyWords,Others FROM [MoneyRecord] t'
+' where (select count(1) from [MoneyRecord] where RecordID>t.RecordID and UserName=t.UserName)+1>'
+convert(varchar,@PageSize*(@PageIndex-1))
+' and UserName='''+convert(varchar,@strUserName)+''''
+' order by RecordID desc'

EXEC (@SQLSTR)

GO


使用中存在一个小问题
翻页的时候第一页和第二页是同样的内容，最后一页不见了
比如说：我有27条记录，每页显示5条。分了6页。
第一页显示的是前5条记录，第二页显示的还是同样的5条记录
第三页显示6－10
第四页显示11－15
第五页显示16－20
第六页显示21－25

26－27就看不到
另外，能不能把这个存储过程中的strUserName改为strWhere（搜索条件）
这样，我这个存储过程的扩展性就更强了

麻烦了 ：）

[tings]

顶一下

[tings]

呼唤vivianfdlpw

[tings]

顶

[wgsasd311]

CREATE PROCEDURE [GetCustomersDataPage]
@PageIndex INT, --以1开始
@PageSize INT,
@strUserName VARCHAR(30),
@RecordCount INT OUT,
@PageCount INT OUT

AS
SELECT @RecordCount=COUNT(*) FROM MoneyRecord WHERE UserName=@strUserName
SET @PageCount = CEILING(@RecordCount * 1.0 / @PageSize)

DECLARE @SQLSTR VARCHAR(500)

if @PageIndex<=0
set @PageIndex=1

select * ,identity(int,1,1) AS SID INTO # FROM MoneyRecord WHERE UserName=@strUserName
SET rowcount @pagesize
SET @SQLSTR='select top '+convert(varchar,@PageSize)
+' RecordID,myDateTime,MoneyType,MoneyNum,myKeyWords,Others FROM # where sid>'
+convert(varchar,@PageSize*(@PageIndex-1))
+' order by RecordID desc'

EXEC (@SQLSTR)
set rowcount 0
drop table #

GO

[wgsasd311]

--另外，能不能把这个存储过程中的strUserName改为strWhere（搜索条件）
CREATE PROCEDURE [GetCustomersDataPage]
@PageIndex INT, --以1开始
@PageSize INT,
@strUserName VARCHAR(30),
@RecordCount INT OUT,
@PageCount INT OUT

AS
DECLARE @SQLSTR NVARCHAR(500)

set @sqlstr='SELECT @RecordCount=COUNT(*) FROM MoneyRecord '+@strUserName
exec sp_executesql @sqlstr,N'@recordcount int output',@recordcount output
SET @PageCount = CEILING(@RecordCount * 1.0 / @PageSize)



if @PageIndex<=0
set @PageIndex=1

select * ,identity(int,1,1) AS SID INTO # FROM MoneyRecord WHERE UserName=@strUserName
SET rowcount @pagesize
SET @SQLSTR='select top '+convert(varchar,@PageSize)
+' RecordID,myDateTime,MoneyType,MoneyNum,myKeyWords,Others FROM # where sid>'
+convert(varchar,@PageSize*(@PageIndex-1))
+' order by RecordID desc'

EXEC (@SQLSTR)
set rowcount 0
drop table #

GO

[tings]

to： wgsasd311(自强不息)
运行时提示错误：
无法使用 SELECT INTO 语句向表 '#' 中添加标识列，该表中已有继承了标识属性的列 'RecordID'。

[wgsasd311]

/*
to： wgsasd311(自强不息)
运行时提示错误：
无法使用 SELECT INTO 语句向表 '#' 中添加标识列，该表中已有继承了标识属性的列 'RecordID'。
*/
--另外，能不能把这个存储过程中的strUserName改为strWhere（搜索条件）
CREATE PROCEDURE [GetCustomersDataPage]
@PageIndex INT, --以1开始
@PageSize INT,
@strUserName VARCHAR(30),
@RecordCount INT OUT,
@PageCount INT OUT

AS
DECLARE @SQLSTR NVARCHAR(500)

set @sqlstr='SELECT @RecordCount=COUNT(*) FROM MoneyRecord '+@strUserName
exec sp_executesql @sqlstr,N'@recordcount int output',@recordcount output
SET @PageCount = CEILING(@RecordCount * 1.0 / @PageSize)



if @PageIndex<=0
set @PageIndex=1

select *,sid=(select count(1) from moneyrecord where username=@strusername and RecordID<=a.RecordID) into #
FROM MoneyRecord a WHERE UserName=@strUserName
SET rowcount @pagesize
SET @SQLSTR='select top '+convert(varchar,@PageSize)
+' RecordID,myDateTime,MoneyType,MoneyNum,myKeyWords,Others FROM # where sid>'
+convert(varchar,@PageSize*(@PageIndex-1))
+' order by RecordID desc'

EXEC (@SQLSTR)
set rowcount 0
drop table #

GO