首页 > 代码库 > 使用临时表并且获取分页数据案例
使用临时表并且获取分页数据案例
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