How to get entities from table by name and another multiple nullable parameters from SQL Server table

Viewed 19

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.

0 Answers
Related