SQL ignore part of WHERE if parameter is null

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)

CREATE PROCEDURE myProcedure    @Param1 nvarchar(50),    @Param2 nvarchar(50),    @Param3 nvarchar(50),    @Param4 nvarchar(50)ASBEGIN    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        ENDEND

This should help

regards

Ashutosh Arya

If you mean @param1 is parameter for col1, @param2 is parameter for col2,... etcYou can try this:

CREATE PROCEDURE myProcedure@Param1 nvarchar(50),@Param2 nvarchar(50),@Param3 nvarchar(50),@Param4 nvarchar(50)ASBEGINdeclare @query nvarchar(4000)SET @query='SELECT Id, col1, col2, col3, col4 FROM myTable '+    (case when ((@Param1 is null) and (@Param2 is null) and (@Param3 is null) and (@Param4 is null))    then ''    else        'where '+        (case when @Param1 is not null        then ' col1 like '''+@param1+'%'''+            (case when @param2 is not null then ' AND ' else '' end)        else '' end)+        (case when @Param2 is not null        then ' col2 like '''+@param2+'%'''+            (case when @param3 is not null then ' AND ' else '' end)        else '' end)+        (case when @Param3 is not null        then ' col3 like '''+@param3+'%'''+            (case when @param4 is not null then ' AND ' else '' end)        else '' end)+        (case when @Param4 is not null        then ' col4 like '''+@param4+'%'''        else '' end)    end)exec sp_sqlexec @query