Friday, August 13, 2010

Understanding the ISNULL in select statement MS SQL Server 2008 .

Replaces NULL with specified parameters replacement value.If you need to take the first non-NULL among more than two values, you will need to nest your ISNULL () statements.
The result of ISNULL()  mimics the data type of the first parameter (whether it is NULL or NOT NULL).    ISNULL ()'s readability and good sense naming is an advantage. 

Syntax: -

ISNULL (check_expression, replacement_value)

Arguments: -


check_expression: - Is the expression to be checked for NULL. Check_expression can be of any type.
replacement_value: -Is the expression to be returned if check_expression is NULL. Replacement_value must be of a type that is implicitly convertible to the type of check_expresssion.

create table employee
(
  ID          int,
   name        nvarchar (10),
   salary      int,
   start_date  datetime,
   city        nvarchar (10),
   region      char (1))
  
 insert into employee  values (1,  'Sonu', 40420,  '02/01/94', 'New York', 'W')

  insert into employee values (2,  'Jamesh',14420,  '01/02/95', 'Vancouver','N')
 
 insert into employee  values (3,  'Celia', 24020,  '12/03/96', 'Toronto',  'W')

 insert into employee  values (4,  'Parbhat', 40620,  '11/04/97', 'New York', 'N')

 insert into employee  values (5,  'David', 80026,  '10/05/98', 'Vancouver','W')

 insert into employee  values (6,  'Michel', 70060,  '09/06/99', 'Toronto',  'N')

 insert into employee  values (7,  'Vikash',90620,  '08/07/00', 'INDIA', 'M')

 insert into employee values (8,  'Parkash', 26020,  '07/08/01', 'INDIA','M')

 insert into employee  values (9,  'Mary',  60020,  '06/09/02', 'Toronto',  'W')

Command in SELECT statement.

 select * from employee

The result is displayed below with Select Statement  for the query on table employee.


Command   ISNULL  in SELECT statement.

select ISNULL(ID, 0)  from Employee where ID = 2
select ISNULL(ID, 0)  from Employee where ID = 4
select ISNULL(ID, 0)  from Employee where ID = 6

The result is displayed below with ISNULL in Select Statement for the query on table employee.

No comments:

Recent Posts