Default NULL not Working - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Default NULL not Working

Author

Topic

vmahesh
Starting Member

19 Posts

Posted - 2007-04-18 : 10:08:55

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

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-04-18 : 10:27:28

Please define what you mean by "if the column value is Blank". An example might help as well.

If you are inserting a '' or a NULL or any other value into a column with a default, you are no longer using the default value, right? The default is only used if you don't specify a value for that particular column; that's what "default" means -- use it if nothing else is specified.

Also, keep in mind that if you are trying to insert a '' into an int column, that '' gets converted to 0.

Hope this helps.

- Jeff
http://weblogs.sqlteam.com/JeffS

vmahesh
Starting Member

19 Posts

Posted - 2007-04-18 : 10:53:48

In the Insert Statement the Column is evaluating to NULL ('') because the user did not enetr any value for the field. '' is getting inserted as 0 as you mentioned. Is there any way that '' can be evaluated to NULL instead of 0 ?

- vmrao

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-04-18 : 11:02:24

You can do something like this using CASE:

insert into YourTable (YourColumn)
values (case when @Param = '' then NULL else @Param end)

or, there is a function called NULLIF() that returns NULL values if the two arguments passed in are equal:

insert into YourTable (YourColumn)
values (NULLIF(@Param,''))

Even better is to validate and convert your data at your front end, and pass only an integer value or NULL to SQL Server via a strongly typed parameter. If SQL expects an integer, your front end should NOT be providing strings.

- Jeff
http://weblogs.sqlteam.com/JeffS

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-04-18 : 11:02:31

[code]NULLIF(column-name, '')[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

vmahesh
Starting Member

19 Posts

Posted - 2007-04-18 : 11:33:21

Thanks. NULLIF has solved my Problem.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-18 : 11:49:05

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=9&messageid=359247

Peter Larsson
Helsingborg, Sweden