Stored procedure with 2 Like parameters

I am looking to use a stored procedure to filter my datagridview by either the user entering the part name into txtPartName or MRPID into txtMRPID. However, this is not filtering as expected and just shows all results from the parts table regardless.

I have a feeling this is due to the way I have implemented the AND or perhaps that I can’t perform 2 LIKE statements in one procedure perhaps? Anyway can someone point me in the right direction as how to properly perform this procedure.

CREATE PROCEDURE Parts_ViewAllOrSearch
    @PartNameSearch nvarchar(255),
    @MRPIDSearch nvarchar(255)
AS
BEGIN
    SELECT *
    FROM Parts
    WHERE @PartNameSearch = ''
       OR PartName LIKE '%' + @PartNameSearch + '%'
      AND @MRPIDSearch = ''
       OR MRP_ID LIKE '%' + @MRPIDSearch + '%'
END

Answer

Basically you need parentheses around the OR condition:

SELECT *
FROM Parts
WHERE 
    (@PartNameSearch = ''  OR  PartName LIKE '%' + @PartNameSearch + '%')
    AND (@MRPIDSearch = '' OR MRP_ID LIKE '%' + @MRPIDSearch + '%')

Why you need that is because AND has higher logical prescedence than OR. So without the parentheses, the WHERE clause is equivalent to:

WHERE 
    @PartNameSearch = ''  
    OR (PartName LIKE '%' + @PartNameSearch + '%' AND @MRPIDSearch = '')
    OR MRP_ID LIKE '%' + @MRPIDSearch + '%'

… which obviously is not what you want.

Finally, please note that, as it stands, your code just does not seem to really these OR expressions. If a parameter is the empty string, then, once surrounded with wildcards it will match on all possible values (except null values). You would just write this as:

WHERE 
    PartName LIKE '%' + @PartNameSearch + '%'
    AND MRP_ID LIKE '%' + @MRPIDSearch + '%'