I am making small e-shop for fun and want to filter results from searching entities by name and/or filter. If name is empty or null, it will get all entities (that works fine), but if I want filter it by category or discount or if its new (always just one filter and/or name), it ignores that completely. How to search it by name, by name and filter, or just filter correctly?
Code is here:
DECLARE @Name AS VARCHAR(100) = '',
@CategoryId AS INT = null,
@IsNew AS BIT = 0,
@IsDiscount AS BIT = 0;
SELECT
*
FROM
Products P
WHERE
(P.Name COLLATE Latin1_General_CI_AI LIKE '%' + COALESCE( @Name, '' ) + '%')
AND (P.CategoryId = @CategoryId OR P.New = @IsNew
OR IIF(P.PriceDiscount IS NULL, 0, 1) = @IsDiscount)
Thanks a lot.