select statement retrieving default value if no data presnt

Question

0.00/5 (No votes)

See more:

Hi all,

I have select statement as

select EmpName from Employee
it returns output as
EmpName
SUNIL
MAHESH
AJAY
VIJAY

if i do it like
select EmpName from Employee where EmpName='SU'
it returns
EmpName

with Empty values
because this SU is not present in database
i want my final statement to retrieve some default value ex: 0
or some text which i can change later.

so my
select EmpName from Employee where EmpName='SU'
should return
EmpName
0

i want to make it succeed with less sql code

as i have bunch of parameter which needs to retrieve different values for different columns as default.

Thanks,
SUNIL MALI.

Posted 5-Mar-13 21:24pm

sunil mali

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

gvprabu · Accepted Answer · 2013-03-05T21:41:00

Solution 1

Hi

Try to Check Record Exists or not....

SQL

SELECT ISNULL(T.Col1,0) 'Col1',
ISNULL(T.Col2,0) 'Col2',
ISNULL(T.Col3,0) 'Col3'
FROM (
SELECT Col1, Col2, Col3 
FROM TableName WHERE Condition
UNION ALL
SELECT NULL, NULL, NULL  WHERE NOT EXISTS(SELECT 1 FROM TableName WHERE Condition)
) T

Regards,
GVPrabu

Posted 5-Mar-13 21:41pm

gvprabu

Updated 5-Mar-13 23:30pm

v2

Comments

sunil mali 6-Mar-13 5:17am

Hi GVPrabhu

i would have done it this way
but i have list of parmeters may be 40 to 50
and i cant do it for all like what you have done

is there a way where we can use case in select statement itself and get this output
i want something like this
i dont know if its possible
select (if exists(select empname from employe) else (select '0')),......
this becomes easier to get values... and i can use it for all my 50 parameters.

Thanks

gvprabu 6-Mar-13 5:30am

hi..
I updated my solution...
Check try like this... Don't add case Statement for all columns

sunil mali 7-Mar-13 2:49am

Superb Solution sir....

i tried it like
SELECT ISNULL(T.UserId,0) from(select UserId from LoginTemp where UserId='005'
union all select NULL where not Exists(select 1 from LoginTemp where UserId='005'))T

its same but with my table
i marked it as answer but still if u can make it much more easier and less code it will be great...
Still thanks and great logic..
Thank you so much....

gvprabu 7-Mar-13 3:15am

always welcome :-)