100分求助一个存储过程

Question

DECLARE  @SQLSTR  VARCHAR(500)   =>
DECLARE  @SQLSTR  NVARCHAR(500)

Answer 1

CREATE     PROCEDURE  [GetCustomersDataPage]    
                 @PageIndex  INT,  
                 @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)    
DECLARE  @MINID  INT   
 
IF  @PageIndex  =  0    
begin  
       SET  @SQLSTR  ='SELECT  TOP '+convert(varchar,@PageSize  )
                      +' RecordID,myDateTime,MoneyType,MoneyNum,myKeyWords,Others FROM  [MoneyRecord]' 
                      +' where  UserName  =  '''+convert(varchar,@strUserName)+''''
                      +' Order  by  RecordID  desc'  
end  
 
ELSE      
begin   
       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)>'
                    +convert(varchar,@PageSize*@PageIndex)
end  
 
EXEC  (@SQLSTR)
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

Answer 2

CREATE     PROCEDURE  [GetCustomersDataPage]    
                 @PageIndex  INT,  
                 @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)    
DECLARE  @MINID  INT   
 
IF  @PageIndex  =  0    
begin  
       SET  @SQLSTR  ='SELECT  TOP '+convert(varchar,@PageSize  )
                      +' RecordID,myDateTime,MoneyType,MoneyNum,myKeyWords,Others FROM  [MoneyRecord]' 
                      +' where  UserName  =  '''+convert(varchar,@strUserName)+''''
                      +' Order  by  RecordID  desc'  
end  
 
ELSE      
begin   
       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)>'
                    +convert(varchar,@PageSize*@PageIndex)
                    +' and UserName='''+convert(varchar,@strUserName)+''''
end  
 
EXEC  (@SQLSTR)
GO

Answer 3

--创建测试环境 
create table MoneyRecord
(
  RecordID int,
  UserName varchar(10),
  myDateTime datetime,
  MoneyType int,
  MoneyNum int,
  myKeyWords varchar(10),
  Others varchar(10)
)
insert MoneyRecord(RecordID,UserName) select 1,'AAA'
insert MoneyRecord(RecordID,UserName) select 2,'BBB'
insert MoneyRecord(RecordID,UserName) select 5,'CCC'
insert MoneyRecord(RecordID,UserName) select 6,'DDD'
insert MoneyRecord(RecordID,UserName) select 7,'AAA'
insert MoneyRecord(RecordID,UserName) select 8,'GGG'
insert MoneyRecord(RecordID,UserName) select 12,'AAA'
insert MoneyRecord(RecordID,UserName) select 23,'AAA'
insert MoneyRecord(RecordID,UserName) select 45,'DDD'
insert MoneyRecord(RecordID,UserName) select 9,'GGG'
insert MoneyRecord(RecordID,UserName) select 29,'KKK'
go--创建存储过程
CREATE     PROCEDURE  [GetCustomersDataPage]    
                 @PageIndex  INT,  
                 @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)    
DECLARE  @MINID  INT   
 
IF  @PageIndex  =  0    
begin  
       SET  @SQLSTR  ='SELECT  TOP '+convert(varchar,@PageSize  )
                      +' RecordID,myDateTime,MoneyType,MoneyNum,myKeyWords,Others FROM  [MoneyRecord]' 
                      +' where  UserName  =  '''+convert(varchar,@strUserName)+''''
                      +' Order  by  RecordID  desc'  
end  
 
ELSE      
begin   
       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)>'
                    +convert(varchar,@PageSize*@PageIndex)
                    +' and UserName='''+convert(varchar,@strUserName)+''''
end  
 
EXEC  (@SQLSTR)
GO--测试
declare @RecordCount int
        ,@PageCount int     --第一页
exec GetCustomersDataPage @PageIndex=0
                          ,@PageSize=2
                          ,@strUserName='AAA'
                          ,@RecordCount=@RecordCount out
                          ,@PageCount=@PageCount outselect @RecordCount '总记录数',@PageCount as '总页数'--结果
/*
RecordID    myDateTime                                             MoneyType   MoneyNum    myKeyWords Others     
----------- ------------------------------------------------------ ----------- ----------- ---------- ---------- 
23          NULL                                                   NULL        NULL        NULL       NULL
12          NULL                                                   NULL        NULL        NULL       NULL（所影响的行数为 2 行）总记录数        总页数         
----------- ----------- 
4           2（所影响的行数为 1 行）
*/
     --第二页
exec GetCustomersDataPage @PageIndex=2
                          ,@PageSize=2
                          ,@strUserName='AAA'
                          ,@RecordCount=@RecordCount out
                          ,@PageCount=@PageCount outselect @RecordCount '总记录数',@PageCount as '总页数'--结果
/*
RecordID    myDateTime                                             MoneyType   MoneyNum    myKeyWords Others     
----------- ------------------------------------------------------ ----------- ----------- ---------- ---------- 
1           NULL                                                   NULL        NULL        NULL       NULL
7           NULL                                                   NULL        NULL        NULL       NULL（所影响的行数为 2 行）总记录数        总页数         
----------- ----------- 
4           2（所影响的行数为 1 行）
*/
--删除测试环境
drop procedure GetCustomersDataPage
drop table MoneyRecord

Answer 4

呵呵
谢谢vivianfdlpw() 
调通了
第一页，第二页都能正确显示
第三页以后显示的都是第二页内容 :)
人笨，没办法
在帮我看看吧

Answer 5

不好意思,疏忽了:                 @PageIndex  INT,  
                 @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)    
DECLARE  @MINID  INT   
       
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

Answer 6

--创建存储过程
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