SQL ignore part of WHERE if parameter is null

Question

I have a stored procedure that fetches info from a table based on 4 parameters.

I want to get values based on the parameters, but if a parameter is NULL then that parameter isn't checked. So if all 4 parameters is null I would show the entire table.

This is my SP (as you can see, this only works for 1 parameter atm):

CREATE PROCEDURE myProcedure     @Param1 nvarchar(50),     @Param2 nvarchar(50),     @Param3 nvarchar(50),     @Param4 nvarchar(50) AS BEGIN     IF(@Param1 IS NULL)         BEGIN             SELECT Id, col1, col2, col3, col4 FROM myTable         END     ELSE         BEGIN             SELECT Id, col1, col2, col3, col4 FROM myTable WHERE col1 LIKE @Param1+'%'         END END 

Is there some way to do this without having a IF for every possible combination (15 IFs)?

Answer 1

How about something like

SELECT Id, col1, col2, col3, col4  FROM    myTable  WHERE   col1 LIKE @Param1+'%' OR      @Param1 IS NULL 

in this specific case you could have also used

SELECT Id, col1, col2, col3, col4  FROM    myTable  WHERE   col1 LIKE ISNULL(@Param1,'')+'%' 

But in general you can try something like

SELECT Id, col1, col2, col3, col4  FROM    myTable  WHERE   (condition1 OR @Param1 IS NULL) AND     (condition2 OR @Param2 IS NULL) AND     (condition3 OR @Param3 IS NULL) ... AND     (conditionN OR @ParamN IS NULL)