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

Viewed 7783

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.

3 Answers
Related