How can I return zero if no record is match in SQL

Question

0.00/5 (No votes)

See more:

hi!!!!!
i try to get maximum ID where product name is 'Laptop'
if it find ID according to product name then it will return ID
but i want if no ID is find according to product name then sql return zero
please any one tell me how can i do this.
I'm using a code but may be it is not fine

What I have tried:

ALTER PROCEDURE [dbo].[USP_Select_Current_PurchaseItem_QtyPurRate_BAseOnItemName]
(
@ItemName		nvarchar(50)
)
AS
BEGIN
	SELECT      
     max(isnull,([Id]),0) as 'CurrentlyaddeItemId'
   
  FROM [dbo].[Purchasing_Items_Hist]
  where Itemtype=@ItemName
END

Posted 10-Aug-18 8:45am

Fahid Zahoor

Updated 10-Aug-18 10:12am

Add a Solution

Comments

Fahid Zahoor 14-Aug-18 7:32am

how we can do the same thing but using (Sum) not Max
i'm using this but it does not return Zero if there is no available Qty is according to productID beacuse there is no stock add in this productId. please tell me how can i do this.
availableQty have int datatype
select
COALESCE(sum([AvailableQty]),0) as'Avil'
from [dbo].[Stock]
group by [ProductID]
having [ProductID]= '2'

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

ZurdoDev · Accepted Answer · 2018-08-10T10:13:00

Solution 1

You have the right idea, just the wrong syntax. And instead of IsNull I believe you should be using COALESCE.

You want to know when MAX(id) is null. Do this:

SQL

SELECT      
     COALESCE(max([Id]), 0) as 'CurrentlyaddeItemId'
   
  FROM [dbo].[Purchasing_Items_Hist]
  where Itemtype=@ItemName

Posted 10-Aug-18 10:13am

ZurdoDev

Comments

Fahid Zahoor 11-Aug-18 6:01am

Thanks You solve my paroblem

Fahid Zahoor 14-Aug-18 7:03am

how we can do the same thing but using (Sum) not Max
i'm using this but it does not return Zero if there is no available Qty is find
select
COALESCE(sum([AvailableQty]),0) as'Avil'
from [dbo].[Stock]
group by [ProductID]
having [ProductID]= '2'

ZurdoDev 14-Aug-18 7:55am

Because of grouping you are not getting any records back so this approach will not work. You'll need to check IF EXISTS() first.