JRockFL
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 + '%')
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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 ...".
ASKER