Introduction
The main purpose of the article is to solve the data fetching problem with lakhs of records with custom paging using stored procedure.
Using the Code
Stored Procedure has parameters as listed below:
Parameter Name | Type | Remark |
@StartRowIndex | int | Start row index |
@MaxRows | int | Page size |
@OrderByField | nvarchar(200) | Order by field name |
@Asc | bit | If 1 then in Asc order else Desc |
@SearchField | nvarchar(200) | Field for search |
@SearchValue | nvarchar(200) | Search field value |
This is the stored procedure given below:
ALTER PROCEDURE [dbo].[GetProducts]
@StartRowIndex int,
@MaxRows int,
@OrderByField nvarchar(200),
@Asc bit,
@SearchField nvarchar(200),
@SearchValue nvarchar(200)
AS
BEGIN
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
Points of Interest
In this article, I have learnt an interesting function ROW_NUMBER()
& sp_Executesql
which is very useful nowadays. You can easily understand how to write a dynamic query.
History
- 30th January, 2010: Initial version