SQL ignore part of WHERE if parameter is null

You can use use COALESCE() function in SQL server. You don't need to use IF- Else or CASE in your statements. Here is how you can use COALESCEfunction.

SELECT Id, col1, col2, col3, col4 FROM myTable where col1 = COALESCE(NULLIF(@param1, ''), col1) and col2 = COALESCE(NULLIF(@param2, ''), col2) and col3 = COALESCE(NULLIF(@param3, ''), col3) and col4=
COALESCE(NULLIF(@param4, ''), col4)

The COALESCE function in SQL returns the first non-NULL expression among its arguments. Here for example if the @param1 is equal to null the function will return col1 which will lead to col1=col1 in the where statement which is like 1=1 meaning the condition will always be true.

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+'%' OR @Param1 is Null
        END
END

This should help

regards

Ashutosh Arya

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)