Where If Condition Is Not Met Get All Values Including Null

Either get only those userIDs with the specified @Lastname or if @LastName = 'All' then get all userIDs even if they have NULL values as their LastName. SELECT userID INTO #table

Solution 1:

Change your condition to this instead:

where (@LastName = 'All' and 1=1) or (LastName = @LastName)

The idea is that when @LastName = 'All' then your query should not have any filters. The 1=1 condition will be always true, and together with the other check should return all results. The other path the query can take is filtering by a specific Last Name, which should definitely exclude null values.

Update: THE 1=1 condition is redundant. You can rewrite the condition as:

where (@LastName = 'All') or (LastName = @LastName)

Demo

Solution 2:

Another shorter form

where @LastName in ('All', LastName);

SqlFiddle

Solution 3:

SELECTuserIDFROMusersWHERECASEWHEN@LastName = 'All' THEN LastName = LastName
    ELSE LastName = @LastNam END
;

Solution 4:

you can also try it-

SELECT * FROMusersWHEREIF(@lastname<>'all',lastname=@lastname,1=1);

In case @lastname contains multiple last name then you can use below query-

SELECT * FROMusersWHEREIF(@lastname<>'all',FIND_IN_SET(lastname,@lastname),1=1);