Question 103596 in Azure Question posted in
The official documentation to get you started can be found here.

How to fetch a result of an int column data type from a table and if no record found select 0 as default in microsoft sql azure

How to fetch a result of an int column data type from a table and if no record found select 0 as default in microsoft sql azure

I had tried ISNULL but it does not work.

In case there is a table named student.

Column_Name data_type

roll_nbr int

name varchar(30)

Select ISNULL(roll_nbr,0) FROM student where name = ‘Sam;

I am expecting the query to return roll_nbr from the table if exists else return 0 as default if no rows is found.

Tags:

2

Answers


  1. 0 
    SELECT TOP 1 
CASE 
WHEN EXISTS(SELECT 1 FROM Student WHERE name = 'Sam') THEN roll_nbr
ELSE 0
END
FROM Student
    Login or Signup to reply.
  2. 0

    You can modify your Azure Sql Query as below

    Using COALESCE()

    SELECT COALESCE((SELECT Roll_nbr FROM student WHERE NAME = 'Sam'), 0);

    (http://sqlfiddle.com/#!18/9a6eaf/2)

    The subquery will return the RollNumber if satisfies the where condition else it will return blank/null result. Use of COALESCE function on subquery will return first not null value.

    Using ISNULL()

    SELECT ISNULL((SELECT Roll_nbr FROM student WHERE NAME = 'Sam'), 0)

    http://sqlfiddle.com/#!18/9a6eaf/6

    Instead of COALESCE() ISNULL() can be used.
    ISNULL function accepts only 2 arguments, used to replace null value and it is T-SQL function.
    COALEASCE function is ANSI SQL Standard based and it accepts 1-n arguments it returns first not null value.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search