Set to zero sets it to NULL

[sheila11]

Hi all,

I have a simple Update statement that goes like this:

Update <Tablename>
Set <Columnname> = 0.00
Where ....

The Column is of Float type, and is set to Null, instead of zero when I call this statement from my application.

Why does that happen? How do I set it to zero?

TIA,
Sheila

 

[gmmastros]

Do you have any triggers on this table?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[apexearth]

Can you debug your program and get the exact sql string that the program is running?

A simple example like this one below shows how the float column isn't set to null when updated to a 0 value. Perhaps your program is converting the value somewhere?

declare @temp table (i float)
insert into @temp
select 0.00
select * from @temp
update @temp set i=0.00
select * from @temp

I know, I'm probably not much help... Sorry

 

[sheila11]

Thanks for your replies guys.

No, I don't have any triggers on that table.

The calling program is not passing the zero value to the statement. The SQL statement is in a Stored procedure, updating the value for a single row and single float-type column.

I also tried using this:
Update <tablename>
Set <columnname> = convert(float, 0.0)
Where . . . ..

But still it saves NULL.

 

[sheila11]

One more thing:
If I call the Stored proc from Management Studio, it works fine, and saves zeros correctly. But when called from application it saves NULL.

 

[apexearth]

Maybe convert it to a string before passing it to SQL? That might stop your program from doing conversions it may be doing.

 

[gmmastros]

If I call the Stored proc from Management Studio, it works fine, and saves zeros correctly. But when called from application it saves NULL.

In this case, it's the application that is converting your 0's to NULL, not the database. You should look there.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[sheila11]

Guys, the application is NOT passing any values to the stored proc.

The stored proc has this update statement, which gets called when certain condition is true. The SP runs correctly otherwise, but instead of replacing any other float value with zero, it replaces it with a NULL.

 

[gmmastros]

Can you show the actual stored procedure code and also the input parameter values (if there are any)?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[sheila11]

There are no input params to the Stored Proc.
The actual statement that does the update is this:

Update Acc_Book
Set Funding = convert(float, 0.00)
WHERE Book = @Book and CloseDate = DateAdd(day, -1, DateAdd( month, DateDiff(month , 0, @CloseDate)+2 , 0) )

@Book is varchar(100)
@CloseDate is varchar(100)

 

[gmmastros]

Is it possible that your where clause is preventing the update? As a test, temporarily add this code....

    Select Count(*) As UpdateCount
    From   Acc_Book
    WHERE Book  = @Book and CloseDate =  DateAdd(day, -1, DateAdd( month, DateDiff(month , 0, @CloseDate)+2 , 0)  )

    Update Acc_Book
    Set Funding = convert(float, 0.00)
    WHERE Book  = @Book and CloseDate =  DateAdd(day, -1, DateAdd( month, DateDiff(month , 0, @CloseDate)+2 , 0)  )

If my hunch is correct, you will notice that UpdateCount returns 0. If it does return 0, then you certainly have a where clause problem that is preventing the actual update.

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

If CloseDate is varchar(100), I think you may want to convert the right side of the comparison to varchar(100) also. In addition, your date format should exactly match what do you have in the CloseDate field. Right now you're getting datetime value which may not match CloseDate format.

PluralSight Learning Library

 

[sheila11]

I am very sorry guys. There was actually no issue with this statement.

The Stored proc is very long, so I couldn't post it here. The issue was with another statement in the Proc, that was resetting the value to null.

Thank you all, and very sorry for the trouble.

Sheila