Return 0 when no data found

SQL Server Administration
1

I am using a stored procedure in which a subquery returning null but i want to replace it with 0 . I'd already used ISNULL , NULLIF and SQL Cases but the result is always same as Empty. How can i Get O in case of null and empty.

Here is my query.

(Select MasterCategoryID from MasterDetails where ID = uinfo.Country) CountryId,

Note : When Uinfo.Country is null it returns CountryId NULL but i want 0 in place of NULL.

2

Not sure if this works, but give it at go:

(select top(1) MasterCategoryID
   from (select 0 rn,MasterCategoryID from MasterDetails where ID = uinfo.Country
         union all
         select 1,0
        ) x
  order by rn
) CountryId
1 Like
3

coalesce((Select MasterCategoryID from MasterDetails where ID = uinfo.Country), 0) As CountryId,

Other options:

coalesce(md.MasterCategoryID, 0) As CountryId,
...
LEFT JOIN MasterDetails md ON md.ID = uinfo.Country

Or:

coalesce(md.MasterCategoryID, 0) As CountryId,
...
OUTER APPLY (Select Top 1 MasterCategoryID From MasterDetails Where ID = uinfo.Country Order By ID) As md

1 Like
4

It solves my problem.
(SELECT ISNULL((Select MasterCategoryID from MasterDetails where ID = uinfo.Country), 0))CountryId,