CODEWITHSUNDEEP
mysqlsql
user3710760
user3710760

Reputation: 567

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 
FROM users 
WHERE LastName = CASE 
                 WHEN @LastName = 'All' THEN LastName 
                 ELSE @LastName END

The above query only returns those userIDs where the LastName is not NULL.

Upvotes: 2

Views: 1917

Answers (4)

Zafar Malik
Zafar Malik

Reputation: 6844

you can also try it-

SELECT * FROM users 
WHERE IF(@lastname<>'all',lastname=@lastname,1=1);

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

SELECT * FROM users 
WHERE IF(@lastname<>'all',FIND_IN_SET(lastname,@lastname),1=1);

Upvotes: 0

Akhil
Akhil

Reputation: 2602

Another shorter form

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

SqlFiddle

Upvotes: 3

shree.pat18
shree.pat18

Reputation: 21757

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

Upvotes: 3

cn0047
cn0047

Reputation: 17061

SELECT userID FROM users
WHERE
    CASE WHEN @LastName = 'All' THEN LastName = LastName
    ELSE LastName = @LastNam END
;

Upvotes: 0

Related Questions