Link to home
Start Free TrialLog in
Avatar of PeteEngineer
PeteEngineerFlag for India

asked on

Neater SQL Code

I am doing the following :

Is there any better way to do this my eliminating the case statements?

select * from Customer where FirstName like ISNULL('ja','') + '%'  AND [EmailId] LIKE ISNULL('jaisonshereen@gmail.com1','') + CASE when 'jaisonshereen@gmail.com1' =  '' then '%' else ''  end

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Dale Burrell
Dale Burrell
Flag of New Zealand image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Erm...
I don't really get what you want to do since ISNULL('Ja','') will always return 'Ja'
Furthermore CASE WHEN 'jaisonshereen@gmail.com1' = '' THEN '%' ELSE '' END will always be '' since 'jaisonshereen@gmail.com1' is never the same as ''
So you could as well just do:
SELECT *
FROM Customer
WHERE FirstName LIKE 'ja%'
AND Emaild LIKE 'jaisonshereen@gmail.com1'

But I think you had something else in mind...
Can you explain the use-case of your statement and what you want to do? The whole use of isnull and case statement here in the provided statement seems to be a dummy one with no proper use.
Unless you will explain what you wanted to do, it will be very hard to assist you.
Avatar of PeteEngineer

ASKER

i fixed as below :


select * from Customer where FirstName = ISNULL('ja',FirstName)
isnull('ja','Firstname') will always give you Firstname = 'ja'.
Why you want to add any other check there.. It is like checking
1=1

I think what you want to do is
isnull(Firstname,'ja')
That doesn't work as well...
It is exactly the same as:
SELECT * FROM Customer WHERE FirstName='ja'

ISNULL returns the second value if the first value is null.
'ja' is not null in any imaginable case so isnull always returns 'ja'

If you describe what you actually want to select I will provide you with the proper query.

Cheers
maybe you're looking for this. So if either of the variables @name or @email are null, those filters will be ignored in the query.
declare @name varchar(20)
declare @email varchar(20)

set @name = 'ja'
set @email = 'jaisonshereen@gmail.com1'


select * 
from Customer 
where 	(@name is null or FirstName = @name)
	AND 
	(@email is null or [EmailId] = @email)

Open in new window

@TempDBA
I don't think he is looking for isnull(Firstname,'ja') since in his query this would be WHERE Firstname=ISNULL(Firstname,'ja') and that would make no sense at all.
When Firstname is not NULL the equation is always true and if it's null then it's NULL='ja' and NULL equates to everything so the equation would be true again...

@PeteEngineer:
Can you try to describe what you have in mind?
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Chris McGuigan
Hi Pete,

Here's the most condensed form I can think of.
"COALESCE" is the same as "ISNULL" but can have more than 2 values and is no slower so I use it instead of ISNULL as a matter of course.
So you want the ISNULL/COALESCE around the the value your checking NOT the literal value you are checking against.
You seem to be making that mistake throughout.

Hope that helps.


Select *
	From Customer
	Where Coalesce(FirstName, '') Like 'ja%'
		And Coalesce(EmailId, '') Like 'jaisonshereen@gmail.com1%'

Open in new window

SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Hi Clamps,

I see where you are coming from.
But if you have an expression with one side = null, it makes the expression false which would falsify the whole thing.
Again your point that the code might be easier for an occasional coder is still valid with this but it may be even simpler without the null check.

I also think the Coalesce/IsNull is more efficient than the "NOT FirstName IS NULL" bit but it's probably negligible in this case.

Cheers
Chris

SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial