• Status: Solved
• Priority: Medium
• Security: Public
• Views: 228

# How to fix this query?

ALTER FUNCTION [dbo].[Func_Bank_Balance]
(
@accountid int,
)
Returns decimal(18,4)
AS
BEGIN
Declare @return decimal(18,4)
Declare @return1 decimal(18,4)
Declare @bal int

select @bal=a.balance from account a where a.id=@accountid
if @bal is null
set @bal = 0

select @return1 = sum(amount + countervalue) from alltransaction
where accountid=@accountid and trandatetime < @addoneday and accountid > 0
if @return1 is null
set @return1 = 0

set @return = @bal + @return1
return @return
END

The result of
select a.balance from account a where a.id='12148'
2.82

The result of
select sum(amount + countervalue) from alltransaction where accountid='12148' and trandatetime < '2009-12-06 06:00:00' and accountid > 0
null

The result of
Select dbo.Func_Bank_Balance('12148', '2009-12-06 06:00:00') accountbal from alltransaction t where t.trandatetime < '2009-12-06 06:00:00'
2.0000

It is wrong, it should be 2.8200

How should I fix it?

0
techques
2 Solutions

Commented:
Shouldn't declare @bal int be declare @bal decimal(18,4)?
0

PluritechnicianCommented:
Hello techques,

Use ISNULL : http://msdn.microsoft.com/en-us/library/aa933210(SQL.80).aspx

Regards.

``````ALTER FUNCTION [dbo].[Func_Bank_Balance]
(
@accountid int,
)
Returns decimal(18,4)
AS
BEGIN
Declare @return decimal(18,4)
Declare @return1 decimal(18,4)
Declare @bal int

select @bal=a.balance from account a where a.id=@accountid
if @bal is null
set @bal = 0

select @return1 = sum(ISNULL(amount,0) + ISNULL(countervalue,0)) from alltransaction
where accountid=@accountid and trandatetime < @addoneday and accountid > 0
if @return1 is null
set @return1 = 0

set @return = @bal + @return1
return @return
END
``````
0

Commented:
try this

Select dbo.Func_Bank_Balance(a.balance) accountbal
from alltransaction t
where t.trandatetime < '2009-12-06 06:00:00'
0

Author Commented:
@bal decimal(18,4) yes, it has fixed
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.