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;

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

---

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?

---