CODEWITHSUNDEEP
sqlsql-servert-sql
J. Davidson
J. Davidson

Reputation: 3307

Returning zeros if null

I have a select statement 

  select 
  name
  ,age
  from table_employees
  where id=@impId;

I want to check if age is null than return zero. I tried following but it doesn't work

     select 
     name
     ,age isnull(age,0.00)
     from table_employees
     where id=@impId;

Please let me know how to fix this. Thanks

Upvotes: 1

Views: 143

Answers (7)

user2191247
user2191247

Reputation:

Suggest the often overlooked COALESCE:

select 
   name, 
   coalesce(age, 0.00) as age_not_null 
from table_employees 
where id = @impId;

Upvotes: 1

Minoru
Minoru

Reputation: 1730

ISNULL is the statement for SQL Server. You can't put the column name before it.

So, try this:

SELECT name, ISNULL(age,0.00) AS age
FROM table_employees
WHERE id=@impId;

Upvotes: 0

Rahul Tripathi
Rahul Tripathi

Reputation: 172378

You can try this:-

select 
    name
,   COALESCE(age,0) as age
from table_employees
where id=@impId;

Upvotes: 1

Robert
Robert

Reputation: 25753

Try this way 

select 
name,age=isnull(age,0.00)
from table_employees
where id=@impId;

or

select 
name,
isnull(age,0.00) as age 
from table_employees
where id=@impId;

Upvotes: 1

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 726479

In SQL Server 2005 or later, you can use the COALESCE function for that:

SELECT
    name
,   COALESCE(age, 0) as age
FROM table_employees
WHERE id=@impId

The function evaluates its arguments one by one, and returns the first non-NULL value.

Upvotes: 1

Sewder
Sewder

Reputation: 754

Try:

 SELECT name,isnull(age,0) 
 FROM table_employees
 WHERE id=@impId;

Upvotes: 0

Sonam
Sonam

Reputation: 3466

Please try following: 

 select 
 name
 ,isnull(age,0.00)
 from table_employees
 where id=@impId;

Upvotes: 0

Related Questions