存储过程返回空 [英] Stored Procedure Returns Null

问题描述

嗨
我正在尝试创建一个存储过程，该存储过程返回取自两个不同表的值的平衡，请问您可以查看一下并更正我的逻辑或语法错误吗?

Hi
I''m trying to create a stored procedure that returns a balance of values taken from two different tables, can u please look at it and correct my logic or syntax error?

Create Proc [dbo].[GetAmntPaid](
@QNo int)
As
Begin
Select Quote.FullAmt - IsNull(Payments.AmountPaid ,0) As Balance from Quote, Payments 
where Quote.QuoteNo = Payments.QuoteNo
And @QNo = Quote.QuoteNo 
End 

预先谢谢你
问候

Thank you in advance
Regards

推荐答案

大家好，我似乎已经忘记了关键信息，第二行中可能根本不存在该行，所以为什么不是IsNull呢?在职的.

Hey Guys It may seem that I might have forgotten crucial piece of info the row might not exist at all in the 2nd table hence why IsNull wasn''t working.

ALTER Proc [dbo].[GetAmntPaid](
@QNo int)
As
Begin
Select IsNull(Quote.FullAmt, 0.00)  - IsNull(Payments.AmountPaid ,0.00) 
from Quote
Left Join Payments on  Quote.QuoteNo = Payments.QuoteNo 
Where Quote.QuoteNo = @QNo
End 

这很完美
问候

This works perfectly
Regards

尝试一下:-

Try this one :-

Create Proc [dbo].[GetAmntPaid](
@QNo int)
As
Begin
Select Quote.FullAmt - IsNull(Payments.AmountPaid ,0) As Balance from Quote, Payments
where Quote.QuoteNo = Payments.QuoteNo
And Quote.QuoteNo = @QNo
End

这篇关于存储过程返回空的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！