使用暫存資料表並且擷取分頁資料案例

標籤：des   code   get   使用   資料   2014   

USE [LMSEnhance]
GO
/****** Object:  StoredProcedure [dbo].[up_GAB_GetProductInfoList]    Script Date: 2014/6/26 15:01:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

 
CREATE  PROCEDURE [dbo].[up_GAB_GetProductInfoList]
    @ProductGroupCode VARCHAR(10) ,
    @ProductName VARCHAR(100) ,
    @PageIndex INT ,
    @PageSize INT ,
    @TotaltCount INT OUTPUT
AS
    BEGIN 
        CREATE TABLE #tempList
         (
              ProductName NVARCHAR(50) ,
              ProductGroupCode NVARCHAR(10) ,
              ProductGroupName NVARCHAR(100),
              ProductPrice DECIMAL
        )          
        INSERT  INTO #tempList
                SELECT  a.ProductName ,
                             a.ProductGroupCode ,
                             b.Name + ‘(‘ + b.ProductGroupCode + ‘)‘,
                             a.ProductPrice
                FROM    dbo.A_GABProductInfo a
                INNER JOIN dbo.ProductGroup b ON b.ProductGroupCode = a.ProductGroupCode
                WHERE   ( @ProductName = NULL    OR @ProductName LIKE ‘%‘ + @ProductName + ‘%‘ )
                AND ( ISNULL(@ProductGroupCode, 0) = 0 OR a.ProductGroupCode LIKE ‘%‘ + @ProductGroupCode + ‘%‘)

       

        SELECT  @TotaltCount = COUNT(*)
        FROM    #tempList

       

        SELECT  *
        FROM    ( SELECT    ROW_NUMBER() OVER ( ORDER BY ProductGroupCode DESC ) AS RowIndex , *
                      FROM      #tempList
                    ) t
        WHERE   RowIndex > @PageSize * @PageIndex
        AND RowIndex <= @PageSize * ( @PageIndex + 1 )

    END