Link to home
Start Free TrialLog in
Avatar of JRockFL
JRockFLFlag for United States of America

asked on

SQL Help

This query is returning products that do not have a categoryid = 2
I would like to return only the products in with categoryid = 2

What am I doing wrong?

DECLARE @NAME varchar(50)
DECLARE @CategoryID int

SET @NAME = NULL
SET @CategoryID = 2

SELECT
      ROW_NUMBER() OVER (Order BY p.ProductID ASC) AS Row,
      p.Name,
      p.Description,
      p.Price,
      i.ImageID,
      p.QuantityInStock,
      p.SanitizedName,
      p.RetailPrice,
      p.DisplayOnWeb,
      p.SKU,
      p.EstimatedArrivalDate,
      ISNULL(p.AllowPreOrder, 'False') AS AllowPreOrder,
      p.CategoryID
FROM
      Products p
LEFT JOIN
      Images i
ON i.ProductID = p.ProductID AND i.IsDefault = 1
WHERE      
      (@CategoryID IS NULL OR p.CategoryID = p.CategoryID)
AND
      (@Name IS NULL OR p.Name LIKE '%' + @Name + '%')
ASKER CERTIFIED SOLUTION
Avatar of Lee Wadwell
Lee Wadwell
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of JRockFL

ASKER

OMG DAH!!!
Avatar of JRockFL

ASKER

I cant believe i didnt see that. thank you!
I cannot remember the number of times I have done the same ... only to get a pair of fresh eyes to glance at it and go ... "there ...".