Problem with the SQL query

Jignesh,

I think the error is in my sp, since when I execute the sp I get the same result with the bolded lines below in the code, as when I don't write that codes.

So I have this code:

ALTER procedure [dbo].[presmSal]
(
@data DateTime,
@saldo decimal output
)
as
Declare @priem decimal
Declare @isplata decimal
--declare @data DateTime
 
set @data='2012-07-12'
select sum(iznosden) as priem
from blagajna_jspturs
where data=@data
update Kasovizvestaj
set
@saldo= isnull(@saldo,0) + (isnull(@priem,0)) - (isnull(@isplata,0))

select saldo=@saldo from Kasovizvestaj
return

exec presmSal
@data='2012-07-12',
@saldo=45

and the result of the exec is the same with the bolded lines and without them, as if the calculation is not performed.

Nel,
I think use input/output parameter with SQL SP
something like

ALTER procedure [dbo].[presmetajSaldo]
(
@data DateTime,
@saldo decimal output
)
...
...
select @saldo = saldo from Kasovizvestaj
...
...

SqlCommand command1 = new SqlCommand("presmSal", connection);
command1.CommandType = CommandType.StoredProcedure;

command1.Parameters.Add(new SqlParameter("@data", SqlDbType.Decimal, 50, ParameterDirection.Input, false, 0, 50, "data", DataRowVersion.Default, data));
command1.Parameters.Add(new SqlParameter("@saldo", SqlDbType.Decimal, 50, ParameterDirection.InputOutput, false, 0, 50, "saldo", DataRowVersion.Default, saldo));
command1.ExecuteNonQuery();
string newRegionDescription = (string)command1.Parameters["@saldo"].Value;

Go through below link.

http://www.codeproject.com/Articles/15403/Calling-Stored-procedures-in-ADO-NET


hope this will help you.

Yes I did, but it returns only the saldo (the first parameter) not the calculation. For ex. if saldo=45, priem =200, isplata=30 I get saldo=45, not 45+200-30=215. It doesn't calculate.
I executed the stored procedure, and also tested it in the C# code.

Here is the code of the sp:


ALTER procedure [dbo].[presmetajSaldo]
(
@data DateTime,
@saldo decimal
)
as
Declare @priem decimal
Declare @isplata decimal
 
set @data='2012-07-11' 
select sum(iznosden) as priem
from blagajna_jspturs
where data=@data and vp>0 and vp<=10

select sum(iznosden) as isplata
from blagajna_jspturs
where vp>10 and data=@data
update Kasovizvestaj
set

saldo= isnull(@saldo,0) + (isnull(@priem,0)) - (isnull(@isplata,0)) 
select saldo from Kasovizvestaj
return


exec presmetajSaldo
@data='20120712',
@saldo=45
select saldo from Kasovizvestaj

and I got
priem isplata  saldo
 Null  Null   45

Here is how I call the sp from C# :

                    SqlCommand command5 = new SqlCommand("presmSal", connection);
                    command5.CommandType = CommandType.StoredProcedure;
                    command5.Parameters.AddWithValue("@data", data);
               
                    command5.Parameters.AddWithValue("@saldo", saldo1);
                    decimal saldo = Convert.ToDecimal(command5.ExecuteScalar());
                    saldo1 = saldo;

Nel,

Are you check with C# code or Execute SP?

Kindly Share info like how you check you SP?

Thx

hi,

it might your value in @saldo,@priem or @isplata is null so use isnull statement.

try..

set
@saldo= isnull(@saldo,0) + (isnull(@priem,0)) - (isnull(@isplata,0)) 
return

In SQL database

1+ null = null

hope this will help you.