CODEWITHSUNDEEP
sqldatabasesql-server-2008stored-procedures
Johan Hjalmarsson
Johan Hjalmarsson

Reputation: 3493

SQL ignore part of WHERE if parameter is null

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)?

Upvotes: 50

Views: 120788

Answers (8)

Adriaan Stander
Adriaan Stander

Reputation: 166326

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.

Upvotes: 79

Mazen Taha
Mazen Taha

Reputation: 13

Declare @criteria nvarchar (max)
SELECT       col1,col2,col3
FROM            table1
((col1+col2+col3 like '%'+@criteria+'%')  OR ISNULL(@criteria, '') = '')

Upvotes: 1

eleon
eleon

Reputation: 183

This will work and easier to understand, at least for me. The IIF statement is only available in SQL Server 2012 and up, you can replace it with a case statement.

SELECT Id, col1, col2, ... 
FROM    myTable 
WHERE   condition1 = IIF(col1 IS NULL, col1, @Param1)
AND     condition2 = IIF(col2 IS NULL, col2, @Param2)...

Upvotes: 0

RollerCosta
RollerCosta

Reputation: 5176

CREATE PROCEDURE myProcedure
  @Param1 nvarchar(50) **= '',**              //#1
  @Param2 nvarchar(50) **= '',**
  @Param3 nvarchar(50) **= '',**
  @Param4 nvarchar(50) **= ''**
AS
BEGIN
    **SET @Param1 = ISNULL(@Param1, '')**     //#2

    SELECT Id, col1, col2, col3, col4 FROM myTable WHERE col1 LIKE @Param1+'%' 
END
  1. Give default value(empty string '' ) to your params: If caller skip passing these params you will receive an empty string that will work fine with your like clause (ex. myProcedure @Param4 = 'some value' // in this case other parameters will be set to empty string('') @param1 )
  2. Set params to '' to save yourself from null parameters(ex. myProcedure Param1 = null, @param2 = null ... // in this case like won't work on the null values and will give you no record against the parameter, to avoid that do an additional check for null value.

Upvotes: 0

Mayur
Mayur

Reputation: 7

Add Condition in where and using CASE .. WHEN .. THEN..

TRY TO FOLLOWING WAY.

select col1,col2,...colN..from Table 
Where clm1 = CASE WHEN @PARAMETER = 0 THEN **COL1** ELSE **@PARAMETER**

ONCE YOU SET SAME COLUMN NAME INTO WHERE CONDITION THEN YOU RETRIEVE ALL DATA FROM TABLE ELSE IF YOU WANT TO PASS AS PARAMETER THEN RETRIEVE BASE ON CONDITION VALUES FROM TABLE.

Upvotes: -5

Aamir
Aamir

Reputation: 1932

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.

Upvotes: 10

Fauzi88
Fauzi88

Reputation: 713

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

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

Upvotes: 5

Ashutosh Arya
Ashutosh Arya

Reputation: 1168

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

Upvotes: 5

Related Questions