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.

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.

SELECT TOP 1 
CASE 
WHEN EXISTS(SELECT 1 FROM Student WHERE name = 'Sam') THEN roll_nbr
ELSE 0
END
FROM Student