ALTER PROCEDURE [dbo].[aspx_Products_SelectSkipAndTakeWhereDynamic]

(

   @start int,

   @end int,

   @sortByExpression varchar(200),

   @productID int = NULL,

   @productName nvarchar(80) = NULL,

   @supplierID int = NULL,

   @categoryID int = NULL,

   @quantityPerUnit nvarchar(40) = NULL,

   @unitPrice money = NULL,

   @unitsInStock smallint = NULL,

   @unitsOnOrder smallint = NULL,

   @reorderLevel smallint = NULL,

   @discontinued bit = NULL

)

AS

BEGIN

  SET NOCOUNT ON;

  SELECT

  [ProductID],

  [ProductName],

  [SupplierID],

  [CategoryID],

  [QuantityPerUnit],

  [UnitPrice],

  [UnitsInStock],

  [UnitsOnOrder],

  [ReorderLevel],

  [Discontinued]

  FROM [dbo].[Products]

  WHERE

  ([ProductID] = @productID OR @productID IS NULL) AND

  ([ProductName] LIKE '%' + @productName + '%' OR @productName IS NULL) AND

  ([SupplierID] = @supplierID OR @supplierID IS NULL) AND

  ([CategoryID] = @categoryID OR @categoryID IS NULL) AND

  ([QuantityPerUnit] LIKE '%' + @quantityPerUnit + '%' OR @quantityPerUnit IS NULL) AND

  ([UnitPrice] = @unitPrice OR @unitPrice IS NULL) AND

  ([UnitsInStock] = @unitsInStock OR @unitsInStock IS NULL) AND

  ([UnitsOnOrder] = @unitsOnOrder OR @unitsOnOrder IS NULL) AND

  ([ReorderLevel] = @reorderLevel OR @reorderLevel IS NULL) AND

  ([Discontinued] = @discontinued OR @discontinued IS NULL)

  ORDER BY

  CASE WHEN @sortByExpression = 'ProductID' THEN [ProductID] END,

  CASE WHEN @sortByExpression = 'ProductID desc' THEN [ProductID] END DESC,

  CASE WHEN @sortByExpression = 'ProductName' THEN [ProductName] END,

  CASE WHEN @sortByExpression = 'ProductName desc' THEN [ProductName] END DESC,

  CASE WHEN @sortByExpression = 'SupplierID' THEN [SupplierID] END,

  CASE WHEN @sortByExpression = 'SupplierID desc' THEN [SupplierID] END DESC,

  CASE WHEN @sortByExpression = 'CategoryID' THEN [CategoryID] END,

  CASE WHEN @sortByExpression = 'CategoryID desc' THEN [CategoryID] END DESC,

  CASE WHEN @sortByExpression = 'QuantityPerUnit' THEN [QuantityPerUnit] END,

  CASE WHEN @sortByExpression = 'QuantityPerUnit desc' THEN [QuantityPerUnit] END DESC,

  CASE WHEN @sortByExpression = 'UnitPrice' THEN [UnitPrice] END,

  CASE WHEN @sortByExpression = 'UnitPrice desc' THEN [UnitPrice] END DESC,

  CASE WHEN @sortByExpression = 'UnitsInStock' THEN [UnitsInStock] END,

  CASE WHEN @sortByExpression = 'UnitsInStock desc' THEN [UnitsInStock] END DESC,

  CASE WHEN @sortByExpression = 'UnitsOnOrder' THEN [UnitsOnOrder] END,

  CASE WHEN @sortByExpression = 'UnitsOnOrder desc' THEN [UnitsOnOrder] END DESC,

  CASE WHEN @sortByExpression = 'ReorderLevel' THEN [ReorderLevel] END,

  CASE WHEN @sortByExpression = 'ReorderLevel desc' THEN [ReorderLevel] END DESC,

  CASE WHEN @sortByExpression = 'Discontinued' THEN [Discontinued] END,

  CASE WHEN @sortByExpression = 'Discontinued desc' THEN [Discontinued] END DESC

  OFFSET @start ROWS

  FETCH NEXT @end ROWS ONLY