CODEWITHSUNDEEP
sqlsql-serverwhere-clause
Aniruddha K Purohit
Aniruddha K Purohit

Reputation: 53

Selecting all the rows if where condition is null in SQL Server

I want to select all the rows from a temp table when I don't provide any parameters in the where clause.

Below is my stored procedure; #ContactAddressDetails is my temp table where final data is available. @ZipCodeOrigin, @ZipCodeDestination are the parameters

Code:

CREATE PROCEDURE [CR2].[spGetEmailAddressByZipsTypeahead]       
     @LoggedInUserId BIGINT
     ,@ZipCodeOrigin VARCHAR(10) = NULL      
     ,@ZipCodeDestination VARCHAR(10) = NULL    
     ,@searchText varchar(15)    
AS      
BEGIN      
    DECLARE @OfficeId INT, @AccountId INT      

    CREATE TABLE #ContactGroupList (ContactGroupId INT)      

    SELECT      
        @OfficeId = OfficeId,      
        @AccountId = AccountID      
    FROM CR2.vwAcgUserCustomer WITH (NOLOCK)      
    WHERE UserID = @LoggedInUserId      

    -- Find the all Contact Group associated to the User, his/her Office and Account      
    INSERT INTO #ContactGroupList      
        SELECT ContactGroupId      
        FROM CR2.ContactGroup WITH (NOLOCK)      
        WHERE ((OwnerType = 1 AND OwnerId = @AccountId)      
               OR (OwnerType = 2 AND OwnerId = @OfficeId)      
               OR (OwnerType = 3 AND OwnerId = @LoggedInUserId))      

    --Display all the addresses of the above contact groups.      
    SELECT       
        CA.ContactAddressId,          
        CA.Email AS [Email],      
        CA.AddressType,      
        CA.AddressCode  
    INTO  
        #ContactAddressDetails      
    FROM 
        CR2.ContactAddress AS CA WITH (NOLOCK)      
    INNER JOIN 
        #ContactGroupList list ON list.ContactGroupId = CA.ContactGroupId      
    LEFT JOIN 
        CR2.ContactAddressDefaultSettings AS CADS WITH (NOLOCK) ON CADS.ContactAddressId = CA.ContactAddressId      
    LEFT JOIN 
        CR2.ContactAddressDefault CAD WITH (NOLOCK) ON CAD.ContactAddressId = CA.ContactAddressId       
                                                    AND CAD.UserId = @LoggedInUserId         
    WHERE 
        CA.ZipCode In  (@ZipCodeOrigin, @ZipCodeDestination)    
        AND CA.Email LIKE @searchText + '%'      
        AND CA.IsDeleted = 0      
        AND CA.AddressType = 3      

    SELECT *      
    FROM #ContactAddressDetails    

    DROP TABLE #ContactAddressDetails      
    DROP TABLE #ContactGroupList      
END

Upvotes: 1

Views: 90

Answers (2)

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 726479

Replace

WHERE CA.ZipCode IN (@ZipCodeOrigin, @ZipCodeDestination)

with

WHERE (CA.ZipCode In  (@ZipCodeOrigin, @ZipCodeDestination))
   OR (@ZipCodeOrigin IS NULL AND @ZipCodeDestination IS NULL)

This condition will be true when both @ZipCodeOrigin and @ZipCodeDestination are null, or when CA.ZipCode matches one or both of them.

Upvotes: 2

Kobi
Kobi

Reputation: 2524

you can use this predicate to not filter data if both zipcode are null:

( (@ZipCodeOrigin is null and @ZipCodeDestination is null) OR CA.ZipCode In (@ZipCodeOrigin, @ZipCodeDestination) )

Upvotes: 2

Related Questions