自定义分页存储过程

引言

本文的主要目的是使用存储过程解决从数据库中获取大量记录（数十万条）时的自定义分页问题。

Using the Code

存储过程的参数如下：

参数名称	类型	备注
@StartRowIndex	int	起始行索引
@MaxRows	int	页面大小
@OrderByField	nvarchar(200)	排序字段名称
@Asc	bit	如果为 1 则升序，否则降序
@SearchField	nvarchar(200)	搜索字段
@SearchValue	nvarchar(200)	搜索字段值

以下是存储过程：

-- =============================================
-- Author:	Mohan Prajapti	
-- Create date: 30 January 2009
-- Description:	Get Product Details
-- =============================================
ALTER PROCEDURE [dbo].[GetProducts]
@StartRowIndex int,
@MaxRows		int,
@OrderByField	nvarchar(200),
@Asc			bit,
@SearchField	nvarchar(200),
@SearchValue	nvarchar(200)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	DECLARE @ProductTable TABLE(ProductId uniqueidentifier,
				    ProductName nvarchar(200),
				    Description ntext,
				    Quantity int,
				    Price decimal(18,2))	

	DECLARE @WhereQuery nvarchar(max)

	SET @WhereQuery = ' WHERE '

	IF LEN(@SearchField) > 0
		BEGIN
			IF @SearchField = 'ProductName'
				SET	@WhereQuery = @WhereQuery + _
				' p_Name like ''%' + @SearchValue + '%'' '
			ELSE IF @SearchField = 'Quantity'
				SET	@WhereQuery = @WhereQuery +  _
				' p_Quantity =' + @SearchValue + ' '
			ELSE IF @SearchField = 'Price'	
				SET	@WhereQuery =  @WhereQuery + _
				' p_Price =' + @SearchValue + ' '
		END

	DECLARE @OrderQuery nvarchar(max)

	SET @OrderQuery = ' ORDER BY [p_Name] ' + _
		CASE @Asc WHEN 1 THEN ' ASC ' ELSE ' DESC ' END

	IF LEN(@OrderByField) > 0
		BEGIN
			IF @OrderByField = 'ProductName'
				SET	@OrderQuery = ' ORDER BY [p_Name] ' + _
					CASE @Asc WHEN 1 _
					THEN ' ASC ' ELSE ' DESC ' END
			ELSE IF @OrderByField = 'Quantity'
				SET	@OrderQuery = _
					' ORDER BY [p_Quantity] ' +  _
					CASE @Asc WHEN 1 _
					THEN ' ASC ' ELSE ' DESC '  END
			ELSE IF @OrderByField = 'Price'	
				SET	@OrderQuery = ' ORDER BY [p_Price] ' +  _
					CASE @Asc WHEN 1 _
					THEN ' ASC ' ELSE ' DESC '  END		
				
		END	
	
	DECLARE @Query  NVARCHAR(MAX)
	SET @Query = 'select	p_Id as ProductId, 
				p_Name as ProductName,
				p_Description as Description,
				p_Quantity as Quantity,
				p_Price as Price
			from 
			(

	
				SELECT 	ROW_NUMBER() OVER ('+ @OrderQuery +') _
					AS [ROW_NUMBER], 
						[t0].[p_Id], [t0].[p_Name], 
						[t0].[p_Description], 
						[t0].[p_Quantity], 
						[t0].[p_Price]
    					FROM [dbo].[Products] AS [t0]' + _
						@WhereQuery +
			') AS [t1]' +
			     + ' WHERE [ROW_NUMBER] BETWEEN _
			     	@StartRowIndex AND @MaxRows' + @OrderQuery

	PRINT @Query

	INSERT INTO @ProductTable EXEC sp_Executesql @Query,
					N'@OrderQuery nvarchar(max),_
				 	@StartRowIndex int,@MaxRows int',
					@OrderQuery=@OrderQuery,
					@StartRowIndex = @StartRowIndex,
					@MaxRows = @MaxRows
								
	SELECT * FROM @ProductTable			

    SET NOCOUNT OFF;
END
GO

关注点

在本文中，我学习了一个有趣的函数 ROW_NUMBER() & sp_Executesql，它们在当今非常有用。您可以轻松理解如何编写动态查询。

历史

• 2010年1月30日：初始版本