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 2008 Forums
 Transact-SQL (2008)
 Replace NULLs with 0 in tables

Author  Topic 

urmas80
Starting Member

20 Posts

Posted - 2015-05-05 : 02:44:37
Hi All,

There are many articles on replacing NULLs with Zero, but most of them are concentrated on an output while I am looking for a more generic solution with an update.

I have already created a function to do that, but some basic misunderstanding of SQL syntax doesn't let me compile.

Here what I got till now:


IF OBJECT_ID (N'Remove_Nulls', N'IF') IS NOT NULL
DROP FUNCTION dbo.Remove_Nulls;
GO


--New Function
CREATE FUNCTION Remove_Nulls (@OID INT)
RETURNS TABLE
AS
BEGIN

DECLARE @column_name varchar(50)

--CURSOR to get all COLUMNS IN A TABLE
DECLARE column_cursor CURSOR
FOR
SELECT NAME
FROM sys.columns
where object_id = @OID

--update statement
OPEN column_cursor
FETCH NEXT FROM column_cursor INTO @column_name

WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE @OID
SET @column_name = 0
WHERE @column_name IS NULL

FETCH NEXT FROM column_cursor INTO @column_name
END
CLOSE column_cursor
DEALLOCATE column_cursor

END;


1. There is something wrong with my begin statement ...
2. I have request from cursor to declare on @OID ...

My best guess is that i did something wrong in a syntax of the function or using something that is not supposed to be where I am using it.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-05-05 : 06:56:08
there are several problems here. The first one is that the update command cannot take a variable as the table name. you need to use dynamic SQL for that. Second problem is that you are not checking the type of the column before you set it to 0.or even that the column is nullable in the first place.this can cause your function to crash unpredictably. The third problem is just the general approach. it's not really a very good idea to use a cursor to iterate a over the columns and use it to run one update command per column. Imagine that your table is very wide say hundreds of columns and then has hundreds of billions of rows your functional will probably run for hours and hours.

Gerald Britton, MCSA
Toronto PASS Chapter
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2015-05-05 : 07:04:42
Also, functions are not allowed to have side effects.
Change the function to a stored procedure.



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2015-05-05 : 10:07:07
First question:

Why do you want to replace NULL with ZERO?

ZERO is a known value.

NULL means that the value is unknown. It is entirely possible that the record has a value, just we don't know what it is (yet).

If you change that to ZERO then you have lost ANY ability to know which records have a (known) value of Zero, and which were unknown but have now been set to Zero.

"most of them are concentrated on an output "

Yeah that's because it preserves the ZERO / NULL original data, and the "known" / "unknown" state, but provides Zero to the user as a reasonable "best value we know at the moment" solution. (For anything that does not need a forced-numeric value we output a Blank String instead so the user knows the value is Unknown, rather than assuming it is a) "known" and b) "actual value = zero"
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-05-05 : 10:16:55
If you just want to eliminate NULLs in returned row sets, it would be better to set up one view per table that uses the ISNULL() function for columns you want to return default values for.

Gerald Britton, MCSA
Toronto PASS Chapter
Go to Top of Page

urmas80
Starting Member

20 Posts

Posted - 2015-05-07 : 04:57:50
Thank you all for answering, sorry it took me a while to come back.

Regarding, WHY; In my case, sometimes NULL means ZERO, that's why I might need to go all over my table and update the values.

quote:
Originally posted by gbritton

there are several problems here. The first one is that the update command cannot take a variable as the table name. you need to use dynamic SQL for that. Second problem is that you are not checking the type of the column before you set it to 0.or even that the column is nullable in the first place.this can cause your function to crash unpredictably. The third problem is just the general approach. it's not really a very good idea to use a cursor to iterate a over the columns and use it to run one update command per column. Imagine that your table is very wide say hundreds of columns and then has hundreds of billions of rows your functional will probably run for hours and hours.

Gerald Britton, MCSA
Toronto PASS Chapter




Gerald, thank you very much for this analysis.

I guess, I do miss a convert statement, but isn't null feats most of the data types? (I am working mostly with Varchar and Num datatypes).

I understand your point, so, If you have a large table, with multiple columns and records, what would be a best approach updating NULL fields in whole table to a certain value?

Thank you in advance,

Uri
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2015-05-07 : 05:40:13
quote:
Originally posted by urmas80

I guess, I do miss a convert statement, but isn't null feats most of the data types? (I am working mostly with Varchar and Num datatypes).

I understand your point, so, If you have a large table, with multiple columns and records, what would be a best approach updating NULL fields in whole table to a certain value?


DO you really want to set VARCHAR to '0' ? Setting to zero will work for numbers, DATETIME (but not DATE) and varchar (with implicit conversion). It won't work for GUIDs

What I would do is to generate a SQL Script (i.e. using something similar to the code that you have in your function)

Then I would review the code and make sure I wanted to convert all the columns that the script included. I could remove any that were "not appropriate" at that point.

Then I would run the script, safe in knowledge that only the appropriate columns had been updated.

Or just create a VIEW on each table and leave it at that. If you create a VIEW and you subsequently find you've done too many columns, or not enough, you can just change the VIEW. The underlying data is still the same. If more, new, NULLs are added to the database it doesn't matter (you won't have to re-run your script, the VIEW will just silently convert any as appropriate)

You could even rename the table and create the view with the table's original name. All exiting code should continue to work - any SELECT will see ZERO instead of NULL. For simple views then UPDATE/INSERT would still work, if you have more complex situations then you could put an INSTEAD OF trigger on the VIEW to save back to the original table.
Go to Top of Page
   

- Advertisement -