How to skip or ignore where clause when parameter is null or empty

Question

I'm writing a stored procedure about searching the database based on aspx page textbox input text.

This stored procedure works well, but I have a problem.

Sometimes @DeptName and @DutyName parameters are empty or null.

So I want to skip or ignore where clauses when that parameter values are empty. But I am confused as to how to handle this situation.

Please give me your advice.

My stored procedure code:

DECLARE 
        @CompanyCode    varchar(20)
    ,   @DeptName       nvarchar(20)
    ,   @DutyName       nvarchar(20)


SELECT  
        @CompanyCode    = 'h101'
    ,   @DeptName       = 'IT'
    ,   @DutyName       = 'Manager'


SELECT
        U.ADDisplayName                     AS UserName             
    ,   LOWER(U.UserID)                     AS EmpID
    ,   ISNULL(CPN.CompanyName, '')         AS CompanyCode              
    ,   ISNULL(U.DisplayName_Eng, '')       AS DisplayName_Eng      
    ,   ISNULL(DT.DisplayName, '')          AS DeptName             
    ,   ISNULL(DTY.DutyName, '')            AS DutyName             
    ,   ISNULL(U.CellPhone, '')             AS CellPhone            
    ,   ISNULL(U.ExtensionNumber, '')       AS ExtensionNumber      
    ,   ISNULL(U.FaxNumber, '')             AS FaxNumber            
    ,   ISNULL(U.ChargeJob, '')             AS ChargeJob            
    ,   ISNULL(LOC.LocationName, '')        AS LocationName         
    ,   ISNULL(U.Workplace, '')             AS Workplace            
    ,   ISNULL(U.EMail, '')                 AS EMail                

FROM dbo.tb_User U 
    INNER JOIN dbo.tb_Dept DT
        ON U.MainDeptCode = DT.DeptCode
    INNER JOIN dbo.tb_Company CPN
        ON U.CompanyCode = CPN.CompanyCode
    INNER JOIN dbo.tb_Location LOC
        ON U.LocationCode = LOC.LocationCode
            AND U.CompanyCode = LOC.CompanyCode
            AND U.GroupCode = LOC.GroupCode
            AND U.DetailCode = loc.DetailCode
    INNER JOIN dbo.tb_Duty DTY
        ON U.DutyCode = DTY.DutyCode
            AND U.CompanyCode = DTY.CompanyCode
            AND U.GroupCode = DTY.GroupCode
            AND U.DetailCode = DTY.DetailCode
WHERE   U.CompanyCode   = @companyCode 
    AND DT.DisplayName  like '%' + @DeptName + '%'
    AND DTY.DutyName    like '%' + @DutyName + '%'
Order by DeptName desc

Thank you.

Please take a look on [IsNULL Checker](https://stackoverflow.com/questions/4224991/sql-server-checking-an-input-param-if-not-null-and-using-it-in-where) — OnDoubt, Jun 02 '17 at 09:25

Marc Guillot · Answer 1 · 2017-06-02T09:57:38.617

A very common construction is :

WHERE   U.CompanyCode   = @companyCode 
    AND (@DeptName is null or DT.DisplayName like '%' + @DeptName + '%')
    AND (@DutyName is null or DTY.DutyName   like '%' + @DutyName + '%')
    ...
    ...
with (recompile)

The with (recompile) hint at the end of your statement is telling SQL Server to check the optimization plan "after" replacing the variables by their values. Doing so it allows to completely ignore a condition when its parameter is null, resulting normally in an optimal execution (only on very complex statements you will need to do more in order to help the SQL engine to find the better execution plan).

It's also worth noting that using the with (recompile) clause forces to check for a new plan at every execution (instead of reusing an existing one), so if your sentence is going to be executed several times for second, then you will be better suited using an alternative, like parameterized dynamic SQL. Although this is not the most usual situation.

PS: If your parameter can also be an empty string, then use isnull to check both options. You still need to add the recompilation hint to execute it optimally.

WHERE   U.CompanyCode   = @companyCode 
    AND (isnull(@DeptName, '') = '' or DT.DisplayName like '%' + @DeptName + '%')
    AND (isnull(@DutyName, '') = '' or DTY.DutyName   like '%' + @DutyName + '%')
    ...
    ...
with (recompile)

score 0 · Answer 2 · edited Jun 02 '17 at 10:31

0

Add an extra @DeptName is null and @DeptName ="" to each clause:

WHERE   U.CompanyCode   = @companyCode 
    AND ((DT.DisplayName  like '%' + @DeptName + '%') or (@DeptName is null) or (@DeptName =''))
    AND ((DTY.DutyName    like '%' + @DutyName + '%') or (@DeptName is null) or (@DeptName = ''))
Order by DeptName desc

edited Jun 02 '17 at 10:31

Ajay2707

5,690
6
40
58

answered Jun 02 '17 at 09:44

cloudsafe

2,444
1
8
24

Von Abanes · Answer 3 · 2017-06-02T10:36:49.673

You can try this.

Using ISNULL you can replace NULL with a specified replacement value.

In this case, IF @DeptName is null then replace it with value of DT.DisplayName. same applies to @DutyName.

The Logical Function IIF is used to check empty value but this applies starting SQL2012 version, otherwise CASE expression for SQL2008 or later versions.

    WHERE   U.CompanyCode   = @companyCode 
            AND DT.DisplayName  like '%' + ISNULL(IIF(@DeptName = '',NULL,@DeptName),DT.DisplayName) + '%'
            AND DTY.DutyName    like '%' + ISNULL(IIF(@DutyName = '',NULL,@DutyName),DTY.DutyName) + '%'
    Order by DeptName desc

How to skip or ignore where clause when parameter is null or empty

3 Answers3