Problem with the SQL query

Question

Hi Can anybody tell me please what's wrong with my sql query? Here is the code: ALTER procedure [dbo].[presmSal] ( @data DateTime @saldo decimal ) as select sum(iznosden) as priem from blagajna_jspturs where data=@data and vp>0 and vp select sum(iznosden) as isplata from blagajna_jspturs where vp>10 and data=@data update Kasovizvestaj set saldo=@saldo + (priem) - (isplata) return Thanks

Jignesh Trivedi · Answer

hi nel You must take data into any variable. try.. ALTER procedure [dbo].[presmSal] ( @data DateTime @saldo decimal ) as Declare @priem decimal Declare @isplata decimal select sum(iznosden) as priem from blagajna_jspturs where data=@data and vp>0 and vp select sum(iznosden) as isplata from blagajna_jspturs where vp>10 and data=@data update Kasovizvestaj set saldo=@saldo + (@priem) - (@isplata) return hope this will help you.

Nel · Answer

I solved it

Nel · Answer

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 and vp>0 and vp select sum(iznosden) as isplata from blagajna_jspturs where vp>10 and data=@data update Kasovizvestaj set @saldo= isnull(@saldo0) + (isnull(@priem0)) - (isnull(@isplata0)) 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.

Jignesh Trivedi · Answer

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.

Nel · Answer

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 select sum(iznosden) as isplata from blagajna_jspturs where vp>10 and data=@data update Kasovizvestaj set saldo= isnull(@saldo0) + (isnull(@priem0)) - (isnull(@isplata0)) 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;

Jignesh Trivedi · Answer

Nel Are you check with C# code or Execute SP? Kindly Share info like how you check you SP? Thx

Nel · Answer

Jignesh I used the code you wrote me to check if it is null but nothing changed

Jignesh Trivedi · Answer

hi it might your value in @saldo@priem or @isplata is null so use isnull statement. try.. set @ saldo= isnull(@saldo 0) + (isnull(@priem0)) - (isnull(@isplata0)) return In SQL database 1+ null = null hope this will help you.

Nel · Answer

Jignesh I tried that and I thought it worked but now I realise that it throws null value instead of the sum. Can you help me please? Thanks

Nel · Answer

Thank you Jignesh