CODEWITHSUNDEEP
sqlazure
Nishant Prabhakar
Nishant Prabhakar

Reputation: 13

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.

Upvotes: 1

Views: 90

Answers (2)

RitikaNalwaya
RitikaNalwaya

Reputation: 590

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.

Upvotes: 2

shahkalpesh
shahkalpesh

Reputation: 33476

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

Upvotes: 0

Related Questions