| set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go -- =============================================
-- Author: Rory Street
-- Modified date: 10 Jan 2008
-- Description: Get Products with optional keyword search sorting and paging.
--
-- =============================================
ALTER PROCEDURE [dbo].[GetProductsWithPaging]
@StartRowIndex int = 0,
@MaximumRows int = 10,
@SortCol varchar(100),
@SortDir varchar(5),
@AmountFrom decimal(38,11)=null,
@AmountTo decimal(38,11)=null,
@KeyWordSearch varchar(200)=null WITH EXECUTE AS CALLER
AS
BEGIN SET NOCOUNT ON;
with TableCTE as
(
SELECT TOP 500 ProductSKU,ProductTitle,ProductDescription,Price,Category
(ROW_NUMBER() OVER (Order By
CASE WHEN @SortCol='ProductTitle' AND @SortDir='DESC' THEN ProductTitle END DESC,
CASE WHEN @SortCol='ProductTitle' AND @SortDir='ASC' THEN ProductTitle END ASC, CASE WHEN @SortCol='ProductDescription' AND @SortDir='DESC' THEN ProductDescription END DESC,
CASE WHEN @SortCol='ProductDescription' AND @SortDir='ASC' THEN ProductDescription END ASC, CASE WHEN @SortCol='Price' AND @SortDir='DESC' THEN Price END DESC,
CASE WHEN @SortCol='Price' AND @SortDir='ASC' THEN Price END ASC, CASE WHEN @SortCol='Category' AND @SortDir='DESC' THEN Category END DESC,
CASE WHEN @SortCol='Category' AND @SortDir='ASC' THEN Category END ASC, )) AS Row
FROM tblProducts
WHERE
((@KeyWordSearch is null) or (ProductDescription like @KeyWordSearch))
AND
((@AmountFrom is null) or (Price >= @AmountFrom ))
AND
((@AmountTo is null) or (Price <= @AmountTo ))
)
select * from TableCTE
WHERE
Row
BETWEEN
@StartRowIndex +1
AND
@StartRowIndex + @MaximumRows END |