使用临时表并且获取分页数据案例

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