dealing with NULL value - 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)

dealing with NULL value

Author

Topic

dirwin26
Yak Posting Veteran

81 Posts

Posted - 2005-09-20 : 15:35:35

I am getting a NULL value in a temp table and as a result I can't graph the data (i'm assuming this is the error). Can anyone tell me how to get rid of a null value within a column? i tried setting the individual variable to not null, but it didn't work...this is what I get.

Server: Msg 515, Level 16, State 1, Procedure Q_History, Line 64
Cannot insert the value NULL into column 'SR', table 'tempdb.dbo.#res_

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-09-20 : 16:10:35

Post your code.

COALESCE might work for you though. It just depends on what you are doing.

Tara

dirwin26
Yak Posting Veteran

81 Posts

Posted - 2005-09-20 : 16:10:50

update TABLE_NAME set COLUMN_NAME = 0 where COLUMN_NAME is null

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-09-20 : 16:11:06

well what do you need it to be if it is null

you can default it to value someVal by using ISNULL() or COALESCE()

Select <yourColumnName> = isnull(<yourColumnName>,someVal) From <yourTable>...

Corey

Co-worker on The Wizard of Oz "...those three midgets that came out and danced, the freaked me out when I was little. But they are ok now."

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-09-20 : 16:15:41

quote:
Originally posted by dirwin26

update TABLE_NAME set COLUMN_NAME = 0 where COLUMN_NAME is null

That won't work as he is getting the data on INSERT, so the data doesn't already exist for it to be updated.

Tara

dirwin26
Yak Posting Veteran

81 Posts

Posted - 2005-09-20 : 16:35:35

Sorry, I posted the solution above. Thanks for the help.

update #res set sr = 0 where sr is null

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-09-20 : 16:37:09

Sorry didn't see that you were the person posting the update statement as well as the original question. I don't understand how an update of the data fixes an insert error. But glad you fixed it.

Tara

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-21 : 01:16:36

>>Server: Msg 515, Level 16, State 1, Procedure Q_History, Line 64
Cannot insert the value NULL into column 'SR', table 'tempdb.dbo.#res_

I think you will get this error only if you try insert statement without giving value to Not null column as below

Declare @t table(i int, v varchar(10) not null)
insert into @t(i) values(1)
select * from @t

or set Not null column to Null

Declare @t table(i int, v varchar(10) not null)
insert into @t values(1,'324')
update @t set v = null where i=1
select * from @t

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources