Null value Update won't…? | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Null value Update won’t…?

Gurus
Why will the SpecialPrice column NOT take the NULL value when I tell it to! Update_Product
@ProdID int,
@ProdName varchar(50)=Null,
@SpecialPrice money=0,
As
If @ProdName is not null
Begin
Update tblProducts Set [email protected]
Where [email protected]
End If @SpecialPrice <> 0
Begin
Update tblProducts Set [email protected]
Where [email protected]
End If @SpecialPrice = 0
Begin
Update tblProducts Set SpecialPrice=Null
Where [email protected]
End
The column gets updated to blank value in this procedure, but I need it to take back the NULL value. The column is NULLABLE. I need it to return to NULL so that I can use the ISNULL function on SpecialPrice at a later date. The table Update will take place when the client uses the web application I am building. So potentially the SpecialPrice column could be Updated and returned to NULL regularly. I will not be explicitly updating the data. I know NULLs are a funny bunch of guys, so am looking forward once again to your expert advice richgran ps. As a matter of fact, none of the NULL values in my application will revert back to <NULL> when I UPDATE SET =NULL. "…as a thief in the night…"
Which account are you using to execute the procedure? Does that account have update permission on tblProducts?
quote:Originally posted by Adriaan Which account are you using to execute the procedure? Does that account have update permission on tblProducts?
Adriaan… thanks for your reply. I am a bit vague on accounts! I install SQL Sever 2000 and it works! I can backup/restore connect to a remote server through EM. My web app can Update/Delete/Insert all other data. it just will not Update to <NULL> value. Upon your question I checked out the Properties on tblProducts and see that the owner is dbo. Clicking on Permissions (in the Properties) I see there is one User/Database Role/Public, named public. The following boxes SELECT/INSERT/UPDATE/DELETE are all unchecked. Am I looking in the right place? Do I check some of these boxes? Or am I off on a tangent!? Look forward to your help Adriaan! "…as a thief in the night…"
You can ignore ‘public’ – you should not use it, but you cannot remove it either. Looks like the login used by your web app must be a member of a fixed server role (like sysadmin or db_owner) or a fixed database role (like db_datawriter) – perhaps through membership of a Windows user group. What happens if you log on with the same credentials through Query Analyzer, and do the update of the column to NULL – any error messages?
quote:
What happens if you log on with the same credentials through Query Analyzer, and do the update of the column to NULL – any error messages?

ok, i have exec the sp in qa. only passing in the product id. Update to NULL values work! but when i exec the sp through my web app, update to NULL values do not work. asp/ado must be passing in to the sp something that is not a NULL? hows your asp? i’ll chat with the gurus over at ASPFREE. thanks for your advice thus far. all the best for the new year richgran "…as a thief in the night…"
When you set a default in the parameter definition, the default is only applied if the parameter is NOT mentioned when calling the procedure. If the client app supplies NULL for @SpecialPrice, then the default is NOT applied. Note that your app may use an ADO class that crunches the parameters in the background. Next, your procedure does two checks: (1) IF @SpecialPrice <> 0
(2) IF @SpecialPrice = 0 Both checks fail when @SpecialPrice is NULL, because NULL is neither equal to nor different from any value, or even a null value. Solution? Change the second check like this, which will repair the loophole for the default:
IF ISNULL(@SpecialPrice, 0) = 0
quote:
Solution? Change the second check like this, which will repair the loophole for the default:
IF ISNULL(@SpecialPrice, 0) = 0

excellent! i will go and try it out! thanks
richgran
"…as a thief in the night…"
A
you are quite right! it wasn’t that my db would not update to null value but that my comparison statements were wrong. i knew nulls were a funny bunch of fellows. i didn’t know that you can not compare one null with another. thanks for the lesson! very gratefull
richgran "…as a thief in the night…"
]]>