Hi,
I have this stored procedure:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[presmetajSaldo]
(
@idki int,
@data DateTime,
@saldo numeric(12,3) output
)
as
Declare @priem numeric(12,3)
Declare @isplata numeric(12,3)
select @priem = sum(iznosden) from blagajna_jspturs
where data=@data
set @saldo = isnull(@saldo,0) + (isnull(@priem,0)) - (isnull(@isplata,0))
update Kasovizvestaj
set
saldo=@saldo
where data=@data
select saldo from Kasovizvestaj
return
When I erase the bold row, the sp calculates correctly, but when I put that row in the sp, it doesn't calculates. Without select saldo from Kasovizvestaj row, it throughs a message "0 rows affected".
Can anybody help me please with a suggestion what's wrong with my sp?
I also tried with:
where CONVERT(VARCHAR(10),data,104) = CONVERT(VARCHAR(10),@data,104)
instead of
where data=@data
but without success.
Thanks a lot in advance.
Loading
Santhosh Kumar JayaramanPosted Aug 1, 2012, 3:17 AM
WHen i tried with the above query it returned null , because it tries to compare
'2005/07/06' with '2005-07-06'.
So i tried with the following approach and i got accurate results.
select CONVERT(VARCHAR(10),InsertDate,101) from report where CONVERT(VARCHAR(10),InsertDate,111)=Replace('2005-07-06','-','/')
you also should try the same,
select @priem = sum(iznosden) from blagajna_jspturs
where CONVERT(VARCHAR(10),data,111)=Replace(@data,'-','/')
update Kasovizvestaj
set
saldo=@saldo
where CONVERT(VARCHAR(10),data,111)=Replace(@data,'-','/')
If the above query fails, then it might be because conversion of @data.
So you can try the below one.
select @priem = sum(iznosden) from blagajna_jspturs
where CONVERT(VARCHAR(10),data,111)=CONVERT(VARCHAR(10),@data,111)