Default NULL not working
-
I am using SQL Server 2000. I have a Column with DataType int and default value specified as (null). But, With Insert or Update if the column value is Blank, 0 is getting inserted instead of the desired NULL.
Thanks
-
That's because there's a default value in the column.
Or in insert trigger that update the rows.
Or a default value has been directly coded within the insert statement.
I must add that NULL is the default default for any column that allows null, so no default should be created at all for the default to be NULL.
-
The Column allows NULLs and there is a default value of (null) for the column. There are no triggers. Also, There is no default value in the insert statement. I tried with '' for the column in the insert statement and still 0 is inserted instead of the desired NULL. Any other ideas please ?
Thanks
-
Please provide the create script for the table, and the insert statements you have tried.
-
I was able to resolve my problem using NULLIF. Thanks.
-
Please post your findings so that other members can benefit from you work.
-
Used NULLIF(column-name, '') and that helped in converting '' to NULL.
-
If that worked, and you didn't have nulls showing up before as defaults, then something was populating them with empty strings.
-
I meant the whole insert statment... I'm sure most folks smart enough to use searh engine are also smart enough to use books online
. -
Here is the whole Insert statement
Insert Into catalogItems (categoryId, itemPrice, supplierCompanyId, itemDeliveryDays, itemLimit) Values ('${param.category}', NULLIF('${param.itemPrice}',''), '${param.splrCompany}', '${param.delDays}', NULLIF('${param.itemLimit}',''))
itemPrice is of float datatype and itemLimit is of int datatype. If user did not enter anything for them in the form, 0 was getting inserted into the Table. Using NULLIF , <NULL> is getting inserted as desired.
Hope it helps someone out there.-vmrao
-
Ok, so that was just the UI thatw as not sending the correct information to the stored procedure. You are using stored procedures right?
-
Based on what you have said and done I assume when you application makes the call to load the data using parameters you are adding using "" as the value which is why you ran into this issue. Instead (and depending on your programming language) you need to set the parameter to NULL directly and change only if the incoming value length is greater than 0.
Ex C#
cmdSQL.Parameters.Add(
new SqlParameter("@Manager_ID",SqlDbType.VarChar,10,ParameterDirection.Output,false,0,0,"Manager_ID",DataRowVersion.Current,null));
if (Request.Form("Manager").length > 0
cmdSQL.Parameters["@Manager_ID"].value = Request.Form("Manager").value;
-
I am not using any Stored Procedures.
Its because of the SQL Server behavior. If you try to put '' into a column of int datatype, SQL Server puts a default value of 0. Using NULLIF puts <NULL> instead of 0.
- vmrao
-
I'd be curious to test this one with the profiler, but I don't have .Net installed so looks like I'll have to pass.
AFAIK, sql server will put exactly what you tell it to put, no more, no less.
-
The reason you were getting a zero inserted is that '' converts to 0 when cast as an int. Try this:
select cast('' as int) -- the return value is 0
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply