这个存储过程少了排序和总条数以及首尾页判断(急急！送100分)

Question

color=#000000][/color]ALTER PROC [dbo].[Proc_GetCardSaledRecord]
@PageSize int=5 ,--每页显示条数
@CurrentPageIndex int,--当前页索引
@WhereSql VARCHAR(1000)='' --查询条件
AS
BEGIN
CREATE TABLE #T_CardSaledRecord
(
ID INT,
Type INT,
Consumption VARCHAR(50),
StartSerialNumber INT,
EndSerialNumber INT,
SaledNum INT,
Receiptor VARCHAR(20),
Ausstehend INT,
PaidUp INT,
Validity INT,
UseCount INT,
CardOpenValidity DATETIME,
Creater VARCHAR(50),
CreatTime DATETIME,
OpenCount INT,
Opened INT,
UsedCount INT,
Used INT,
OpenUtilization FLOAT,
UseUtilization FLOAT
)
DECLARE @Type INT,@StartSerialNumber INT,@EndSerialNumber INT,@OpenCount INT,@Opened INT,@Used INT,@UsedCount INT,@ID INT
DECLARE @Consumption VARCHAR(50),@ConsumptionID INT,@SaledNum INT,@Receiptor VARCHAR(20),@Ausstehend INT,@PaidUp INT,@Validity INT,@UseCount INT,@CardOpenValidity DATETIME
DECLARE @Creater VARCHAR(50),@CreatTime DATETIME
DECLARE CardSaledRecord_Cur CURSOR
FOR
SELECT ID,Type,ConsumptionID,StartSerialNumber,EndSerialNumber,SaledNum,Receiptor,Ausstehend,PaidUp,Validity,UseCount,CardOpenValidity,Creater,CreatTime FROM CardSaledRecord
OPEN CardSaledRecord_Cur
FETCH NEXT FROM CardSaledRecord_Cur INTO @ID,@Type,@ConsumptionID,@StartSerialNumber,@EndSerialNumber,@SaledNum,@Receiptor,@Ausstehend,@PaidUp,@Validity,@UseCount,@CardOpenValidity,@Creater,@CreatTime
WHILE @@FETCH_STATUS=0
BEGIN
SELECT @OpenCount=COUNT(1),@UsedCount=SUM(TotalCount),@Used=SUM(useCount) FROM HM_CardDetail WHERE CardType=@Type AND (SerialNumber BETWEEN @StartSerialNumber AND @EndSerialNumber)
SELECT @Opened=COUNT(1) FROM HM_CardDetail WHERE CardType=@Type AND ISNULL(Openuserid,'')<>'' AND (SerialNumber BETWEEN @StartSerialNumber AND @EndSerialNumber)
SELECT @Consumption=Company FROM Consumption WHERE ID=@ConsumptionID
IF @OpenCount=0
INSERT INTO #T_CardSaledRecord VALUES(@ID,@Type,@Consumption,@StartSerialNumber,@EndSerialNumber,@SaledNum,@Receiptor,@Ausstehend,@PaidUp,@Validity,@UseCount,@CardOpenValidity,@Creater,@CreatTime,@OpenCount,@Opened,@UsedCount,@UsedCount-@Used,0,(@UsedCount-@Used)*100/@UsedCount)
ELSE
INSERT INTO #T_CardSaledRecord VALUES(@ID,@Type,@Consumption,@StartSerialNumber,@EndSerialNumber,@SaledNum,@Receiptor,@Ausstehend,@PaidUp,@Validity,@UseCount,@CardOpenValidity,@Creater,@CreatTime,@OpenCount,@Opened,@UsedCount,@UsedCount-@Used,@Opened*100/@OpenCount,(@UsedCount-@Used)*100/@UsedCount)
FETCH NEXT FROM CardSaledRecord_Cur INTO @ID,@Type,@ConsumptionID,@StartSerialNumber,@EndSerialNumber,@SaledNum,@Receiptor,@Ausstehend,@PaidUp,@Validity,@UseCount,@CardOpenValidity,@Creater,@CreatTime
END
CLOSE CardSaledRecord_Cur
DEALLOCATE CardSaledRecord_Cur
IF @WhereSql=''
begin
--@PageSize int ,--每页显示条数
--@CurrentPageIndex int,--当前页索引 从0开始.
WITH tt AS
(
SELECT *,ROW_NUMBER() OVER(ORDER BY ID) AS pid FROM #T_CardSaledRecord 
)
SELECT * FROM tt
WHERE pid > (@CurrentPageIndex-1)*@PageSize AND pid <=@CurrentPageIndex*@PageSize;
end
ELSE
BEGIN
DECLARE @Sql VARCHAR(1000)
SET @Sql='SELECT * FROM #T_CardSaledRecord WHERE 1=1 '+@WhereSql;
SELECT * INTO #T_CardSaledRecord2 FROM #T_CardSaledRecord WHERE 1=2; 
INSERT INTO #T_CardSaledRecord2 EXEC(@Sql); 
WITH ttt AS
(
SELECT *,ROW_NUMBER() OVER(ORDER BY ID) AS pid FROM #T_CardSaledRecord2 
)
SELECT * FROM ttt
WHERE pid > (@CurrentPageIndex-1)*@PageSize AND pid <=@CurrentPageIndex*@PageSize;END
end这个是一个3表连接带参数分页，我想获取他的总页数和按时间排序以及首页和尾页的判断

Answer 1

--  红色部分增加了总页数和首、尾页[其中首页不就是“1”么？]，不知道我理解的对不对
ALTER PROC [dbo].[Proc_GetCardSaledRecord]
@PageSize int=5 ,--每页显示条数
@CurrentPageIndex int,--当前页索引
@WhereSql VARCHAR(1000)='' --查询条件
AS
BEGIN
CREATE TABLE #T_CardSaledRecord
(
ID INT,
Type INT,
Consumption VARCHAR(50),
StartSerialNumber INT,
EndSerialNumber INT,
SaledNum INT,
Receiptor VARCHAR(20),
Ausstehend INT,
PaidUp INT,
Validity INT,
UseCount INT,
CardOpenValidity DATETIME,
Creater VARCHAR(50),
CreatTime DATETIME,
OpenCount INT,
Opened INT,
UsedCount INT,
Used INT,
OpenUtilization FLOAT,
UseUtilization FLOAT
)
DECLARE @Type INT,@StartSerialNumber INT,@EndSerialNumber INT,@OpenCount INT,@Opened INT,@Used INT,@UsedCount INT,@ID INT
DECLARE @Consumption VARCHAR(50),@ConsumptionID INT,@SaledNum INT,@Receiptor VARCHAR(20),@Ausstehend INT,@PaidUp INT,@Validity INT,@UseCount INT,@CardOpenValidity DATETIME
DECLARE @Creater VARCHAR(50),@CreatTime DATETIMEDECLARE CardSaledRecord_Cur CURSOR
FOR
SELECT ID,Type,ConsumptionID,StartSerialNumber,EndSerialNumber,SaledNum,Receiptor,Ausstehend,PaidUp,Validity,UseCount,CardOpenValidity,Creater,CreatTime FROM CardSaledRecordOPEN CardSaledRecord_Cur
FETCH NEXT FROM CardSaledRecord_Cur INTO @ID,@Type,@ConsumptionID,@StartSerialNumber,@EndSerialNumber,@SaledNum,@Receiptor,@Ausstehend,@PaidUp,@Validity,@UseCount,@CardOpenValidity,@Creater,@CreatTime
WHILE @@FETCH_STATUS=0
BEGIN
SELECT @OpenCount=COUNT(1),@UsedCount=SUM(TotalCount),@Used=SUM(useCount) FROM HM_CardDetail WHERE CardType=@Type AND (SerialNumber BETWEEN @StartSerialNumber AND @EndSerialNumber)
SELECT @Opened=COUNT(1) FROM HM_CardDetail WHERE CardType=@Type AND ISNULL(Openuserid,'')<>'' AND (SerialNumber BETWEEN @StartSerialNumber AND @EndSerialNumber)
SELECT @Consumption=Company FROM Consumption WHERE ID=@ConsumptionID
IF @OpenCount=0
INSERT INTO #T_CardSaledRecord VALUES(@ID,@Type,@Consumption,@StartSerialNumber,@EndSerialNumber,@SaledNum,@Receiptor,@Ausstehend,@PaidUp,@Validity,@UseCount,@CardOpenValidity,@Creater,@CreatTime,@OpenCount,@Opened,@UsedCount,@UsedCount-@Used,0,(@UsedCount-@Used)*100/@UsedCount)
ELSE
INSERT INTO #T_CardSaledRecord VALUES(@ID,@Type,@Consumption,@StartSerialNumber,@EndSerialNumber,@SaledNum,@Receiptor,@Ausstehend,@PaidUp,@Validity,@UseCount,@CardOpenValidity,@Creater,@CreatTime,@OpenCount,@Opened,@UsedCount,@UsedCount-@Used,@Opened*100/@OpenCount,(@UsedCount-@Used)*100/@UsedCount)

FETCH NEXT FROM CardSaledRecord_Cur INTO @ID,@Type,@ConsumptionID,@StartSerialNumber,@EndSerialNumber,@SaledNum,@Receiptor,@Ausstehend,@PaidUp,@Validity,@UseCount,@CardOpenValidity,@Creater,@CreatTime
END
CLOSE CardSaledRecord_Cur
DEALLOCATE CardSaledRecord_CurIF @WhereSql=''
begin
--@PageSize int ,--每页显示条数
--@CurrentPageIndex int,--当前页索引 从0开始.
WITH tt AS
(
SELECT *,ROW_NUMBER() OVER(ORDER BY ID) AS pid FROM #T_CardSaledRecord  
)
SELECT *,(select count(1) from tt) AS [总页数],1 首页,(select round(count(1)*1.0/@PageSize,0) from tt) 尾页 FROM tt
WHERE pid > (@CurrentPageIndex-1)*@PageSize AND pid <=@CurrentPageIndex*@PageSize;
end
ELSE
BEGIN
DECLARE @Sql VARCHAR(1000)
SET @Sql='SELECT * FROM #T_CardSaledRecord WHERE 1=1 '+@WhereSql;
SELECT * INTO #T_CardSaledRecord2 FROM #T_CardSaledRecord WHERE 1=2;  
INSERT INTO #T_CardSaledRecord2 EXEC(@Sql);  
WITH ttt AS
(
SELECT *,ROW_NUMBER() OVER(ORDER BY ID) AS pid FROM #T_CardSaledRecord2  
)
SELECT *,(select count(1) from tt) AS [总页数],1 首页,(select round(count(1)*1.0/@PageSize,0) from tt) 尾页 FROM ttt
WHERE pid > (@CurrentPageIndex-1)*@PageSize AND pid <=@CurrentPageIndex*@PageSize;END
end

Answer 2

ALTER PROC [dbo].[Proc_GetCardSaledRecord]
@PageSize int=5 ,--每页显示条数
@CurrentPageIndex int,--当前页索引，首页 1 尾页 -1 全部 0
@WhereSql VARCHAR(1000)='', --查询条件
@Order bit=0,--排序方式 0顺序 1 逆序 
@Pagecount int out --总页数
AS
BEGIN
CREATE TABLE #T_CardSaledRecord
(
ID INT,
Type INT,
Consumption VARCHAR(50),
StartSerialNumber INT,
EndSerialNumber INT,
SaledNum INT,
Receiptor VARCHAR(20),
Ausstehend INT,
PaidUp INT,
Validity INT,
UseCount INT,
CardOpenValidity DATETIME,
Creater VARCHAR(50),
CreatTime DATETIME,
OpenCount INT,
Opened INT,
UsedCount INT,
Used INT,
OpenUtilization FLOAT,
UseUtilization FLOAT
)
DECLARE @Type INT,@StartSerialNumber INT,@EndSerialNumber INT,@OpenCount INT,@Opened INT,@Used INT,@UsedCount INT,@ID INT
DECLARE @Consumption VARCHAR(50),@ConsumptionID INT,@SaledNum INT,@Receiptor VARCHAR(20),@Ausstehend INT,@PaidUp INT,@Validity INT,@UseCount INT,@CardOpenValidity DATETIME
DECLARE @Creater VARCHAR(50),@CreatTime DATETIME
DECLARE CardSaledRecord_Cur CURSOR
FOR
SELECT ID,Type,ConsumptionID,StartSerialNumber,EndSerialNumber,SaledNum,Receiptor,Ausstehend,PaidUp,Validity,UseCount,CardOpenValidity,Creater,CreatTime FROM CardSaledRecord
OPEN CardSaledRecord_Cur
FETCH NEXT FROM CardSaledRecord_Cur INTO @ID,@Type,@ConsumptionID,@StartSerialNumber,@EndSerialNumber,@SaledNum,@Receiptor,@Ausstehend,@PaidUp,@Validity,@UseCount,@CardOpenValidity,@Creater,@CreatTime
WHILE @@FETCH_STATUS=0
BEGIN
SELECT @OpenCount=COUNT(1),@UsedCount=SUM(TotalCount),@Used=SUM(useCount) FROM HM_CardDetail WHERE CardType=@Type AND (SerialNumber BETWEEN @StartSerialNumber AND @EndSerialNumber)
SELECT @Opened=COUNT(1) FROM HM_CardDetail WHERE CardType=@Type AND ISNULL(Openuserid,'')<>'' AND (SerialNumber BETWEEN @StartSerialNumber AND @EndSerialNumber)
SELECT @Consumption=Company FROM Consumption WHERE ID=@ConsumptionID
IF @OpenCount=0
INSERT INTO #T_CardSaledRecord VALUES(@ID,@Type,@Consumption,@StartSerialNumber,@EndSerialNumber,@SaledNum,@Receiptor,@Ausstehend,@PaidUp,@Validity,@UseCount,@CardOpenValidity,@Creater,@CreatTime,@OpenCount,@Opened,@UsedCount,@UsedCount-@Used,0,(@UsedCount-@Used)*100/@UsedCount)
ELSE
INSERT INTO #T_CardSaledRecord VALUES(@ID,@Type,@Consumption,@StartSerialNumber,@EndSerialNumber,@SaledNum,@Receiptor,@Ausstehend,@PaidUp,@Validity,@UseCount,@CardOpenValidity,@Creater,@CreatTime,@OpenCount,@Opened,@UsedCount,@UsedCount-@Used,@Opened*100/@OpenCount,(@UsedCount-@Used)*100/@UsedCount)
FETCH NEXT FROM CardSaledRecord_Cur INTO @ID,@Type,@ConsumptionID,@StartSerialNumber,@EndSerialNumber,@SaledNum,@Receiptor,@Ausstehend,@PaidUp,@Validity,@UseCount,@CardOpenValidity,@Creater,@CreatTime
END
CLOSE CardSaledRecord_Cur
DEALLOCATE CardSaledRecord_Cur
IF @WhereSql=''
begin
--@PageSize int ,--每页显示条数
--@CurrentPageIndex int,--当前页索引 从0开始.
WITH tt AS
(
SELECT *,ROW_NUMBER() OVER(ORDER BY case @order when 0 then cast(CreatTime as int) else cast(CreatTime as int)*-1 end) AS pid FROM #T_CardSaledRecord  
)
SELECT * FROM tt
WHERE pid > (@CurrentPageIndex-1)*@PageSize AND pid <=@CurrentPageIndex*@PageSize;
set @Pagecount=@@ROWCOUNT
end
ELSE
BEGIN
DECLARE @Sql VARCHAR(1000)
SET @Sql='SELECT * FROM #T_CardSaledRecord WHERE 1=1 '+@WhereSql;
SELECT * INTO #T_CardSaledRecord2 FROM #T_CardSaledRecord WHERE 1=2;  
INSERT INTO #T_CardSaledRecord2 EXEC(@Sql);  
WITH ttt AS
(
SELECT *,ROW_NUMBER() OVER(ORDER BY case @order when 0 then cast(CreatTime as int) else cast(CreatTime as int)*-1 end) AS pid FROM #T_CardSaledRecord2  
)
SELECT * FROM ttt
WHERE pid > (@CurrentPageIndex-1)*@PageSize AND pid <=@CurrentPageIndex*@PageSize;
set @Pagecount=@@ROWCOUNT
END
end
--总页数和按时间排序以及首页和尾页的判断这个先测试一下，可以了首尾页的事下午来

Answer 3

版主给楼上的人兄弟加分！
leo_lesley兄弟你最好能能个带参数的总页数 我好获取参数 分页还有那个排序的 按时间排序 加个参数 谢谢了！

Answer 4

http://topic.csdn.net/u/20100414/11/7ba2b45e-0fe2-4f63-a12d-b0e962d9d11d.html

Answer 5

http://topic.csdn.net/u/20100414/11/7ba2b45e-0fe2-4f63-a12d-b0e962d9d11d.html