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)

Note that the first and last method can result in caching of query plans that would be appropriate for one set of parameters, but not others (especially if they have more or less NULL values). As a result, you likely will want to add OPTION (RECOMPILE) to your query as this will result in a new query plan being generated each time the query is run, and avoiding a cached plan being used that could be a poor choice for the parameters.