Filtering by args in Stored Proc

Question

I have a problem with my code: P is clearly right ?! Anyway ... that's what it is ... I want to have a STORED PROC that takes 3 arguments (@cat, @cutPrice, @option) and returns a few results that have a type similar to @cat and a price, which is equal to <or> @cutPrice depending on the @option keyword "above" or "below." The problem is when I do this ...

EXEC spBookByPrice @cat = 'business', @cutPrice = 0.00, @option = 'above' 

... I do not get any results, but @option = '' shows all the prices. Anyway, here is my code ....

 ALTER PROC spBookByPrice @cat varchar(12), @cutPrice money, @option varchar(5) AS BEGIN SELECT title AS 'Title:', type AS 'Category:', price AS 'Price:', CASE WHEN price >= @cutPrice THEN 'above' WHEN price < @cutPrice THEN 'below' --ELSE NULL END AS 'Option:' FROM dbo.titles WHERE 'Option:' LIKE '%' + @option + '%' GROUP BY type, title, price HAVING type LIKE '%' + @cat + '%' END 

Answer 1

That's what you need:

 ALTER PROC spBookByPrice @cat varchar(12), @cutPrice money, @option varchar(5) AS BEGIN SELECT title AS [Title:], type AS [Category:], price AS [Price:], CASE WHEN price >= @cutPrice THEN 'above' WHEN price < @cutPrice THEN 'below' --ELSE NULL END AS [Option:] FROM dbo.titles WHERE (CASE WHEN price >= @cutPrice THEN 'above' WHEN price < @cutPrice THEN 'below' --ELSE NULL END) LIKE '%' + @option + '%' GROUP BY type, title, price HAVING type LIKE '%' + @cat + '%' END 

Answer 2

 select title [Title:], type [Category:], price [Price:], [Option:] = iif(price >= @cutPrice, 'above', 'below') from titles where type like concat('%', @cat, '%') and (@option = '' or (@option='below' and price < @cutPrice) or (@option='above' and price >= @cutPrice))