笔记147 Get存储过程

笔记147 Get存储过程

--的Get存储过程
USE [GPOSDB]
GO
/****** 对象:  StoredProcedure [dbo].[CT_Append_Get]    脚本日期: 02/05/2012 21:50:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-----------------------------------------------------------------
-- Name: CT_Append Store Procedure
-- Author: 
-- DateTime: 2011-07-22
-- Description: Data TableCT_Append , Get List
-----------------------------------------------------------------
/*
@PageIndex
@TotalRecords
*/    
ALTER PROCEDURE [dbo].[CT_Append_Get]
(
     @PageIndex int,
     @TotalRecords int
)
AS
BEGIN
    DECLARE @Page int
     DECLARE @PageLowerBound int
     DECLARE @PageUpperBound int
     DECLARE @RowsToReturn int

     SET @Page = (@PageIndex - 1)

     -- First set the rowcount
     SET @RowsToReturn = @TotalRecords * (@Page + 1)
     SET ROWCOUNT @RowsToReturn

     -- Set the page bounds
     SET @PageLowerBound = @TotalRecords * @Page
     SET @PageUpperBound = @PageLowerBound + @TotalRecords + 1

     -- Create a temp table to store the select results
     CREATE TABLE #PageIndex
     (
          IndexId int IDENTITY (1, 1) NOT NULL,
          VC_A_SNNO int
     )
    
     INSERT INTO #PageIndex (VC_A_SNNO)
     SELECT
          [VC_A_SNNO]
     FROM
          [CT_Append]
         
   
     SELECT
          c.VC_A_SNNO,
          [VC_A_AppendType] ,
          [VC_A_CardNO] ,
          [I_A_CardType] ,
          [I_A_PointToOil] ,
          [VC_TicketType] ,
          [VC_TicketNO] ,
          [DE_A_BAmount] ,
          [DE_A_AppendAmount] ,
          [DE_A_AAmount] ,
          [D_A_AppendDateTime] ,
          [VC_A_Remark] ,
          [VC_A_OperatorNO]
     FROM [CT_Append] c , #PageIndex PageIndex
     WHERE
          c.VC_A_SNNO = PageIndex.VC_A_SNNO AND
          PageIndex.IndexID > @PageLowerBound AND
          PageIndex.IndexID < @PageUpperBound
    
     SELECT COUNT(VC_A_SNNO) AS TotalRecords FROM [CT_Append]
END