Cursor Process Coding Problem

Hi guys,

It's been awhile since I have posted. I have a situation for the group here. I am new to working with cursors. I have a simple one here that I wish to use to update NULL fields in a table called rpt_Scr_B0000_MiniFinancials. I know for a fact that there are NULLs in this table. When I run the select query from the information_schema I get some 60 some odd fields. Anyway, when I run this I get 0 records affected which I know is incorrect. It appears that my cursor is only processing for the first field. I tried changing the @.@.FETCH_STATUS = 0 to @.@.FETCH_STATUS > 0 and that didn't work either. What am I doing wrong? Thx.

DECLARE @.FieldName char (25)

DECLARE cursor_update_rpt_Scr_B0000_MiniFinancials CURSOR For

select column_name
from information_schema.columns
where table_name = 'rpt_Scr_B0000_MiniFinancials'

open cursor_update_rpt_Scr_B0000_MiniFinancials

FETCH NEXT FROM cursor_update_rpt_Scr_B0000_MiniFinancials
INTO @.FieldName

update rpt_Scr_B0000_MiniFinancials
set @.FieldName = 0
where @.FieldName is null

WHILE @.@.FETCH_STATUS = 0
BEGIN

FETCH NEXT FROM cursor_update_rpt_Scr_B0000_MiniFinancials
INTO @.FieldName

END

CLOSE cursor_update_rpt_Scr_B0000_MiniFinancials
DEALLOCATE cursor_update_rpt_Scr_B0000_MiniFinancialsyour update needs to be inside the WHILE Loop

But aren't you worried about datatypes...and what's wrong with nulls annyway

(Here we go again)|||Ok,

I tried that but now I get all zeros. This is a step forward. I have a table with 66 fields and five records. It appears it is now processing each update but there is still something wrong. Say one of the fields, ie 'abc' has 3 records populated leaving two nulls, the two nulls should be turned into zeros. The issue is that the front end programmer creating the view wants me to populate the nulls with zeros. Is it easier if I use a coalesce function in some way instead?

Here is the updated code:

DECLARE @.FieldName char (25)

DECLARE cursor_update_rpt_Scr_B0000_MiniFinancials CURSOR For

select column_name
from information_schema.columns
where table_name = 'rpt_Scr_B0000_MiniFinancials'

open cursor_update_rpt_Scr_B0000_MiniFinancials

FETCH NEXT FROM cursor_update_rpt_Scr_B0000_MiniFinancials
INTO @.FieldName

WHILE @.@.FETCH_STATUS = 0
BEGIN

update rpt_Scr_B0000_MiniFinancials
set @.FieldName = 0
where @.FieldName is null

FETCH NEXT FROM cursor_update_rpt_Scr_B0000_MiniFinancials
INTO @.FieldName

END|||Well I would take a different approach...you do know what happens when you assign a 0 to a datetime column don't you. Anyway, cut and paste this code example into query analyzer...it should run no problem.

USE Northwind
GO

CREATE TABLE myTable99(Col1 int, Col2 char(1), Col3 datetime)
GO

INSERT INTO myTable99(Col1,Col2,Col3)
SELECT 1 , null, '2006-01-01' UNION ALL
SELECT null, 'b' , '2006-01-02' UNION ALL
SELECT 3 , 'c' , null
GO

SELECT * FROM myTable99
GO

DECLARE @.sql varchar(8000), @.collist varchar(8000), @.TABLE_NAME sysname

SET @.TABLE_NAME = 'myTable99'

SELECT @.collist = COALESCE(@.collist+', ','') + COLUMN_NAME + ' = '
+ 'CASE WHEN ' + COLUMN_NAME + ' IS NULL THEN '
+ CASE WHEN DATA_TYPE IN ('char','nchar','varchar','nvarchar','text','ntext ') THEN ''''+'0'+'''' ELSE '0' END
+ ' ELSE ' + COLUMN_NAME + ' END'
FROM INFORMATION_SCHEMA.Columns
WHERE TABLE_NAME = @.TABLE_NAME

SELECT @.sql = 'UPDATE ' + @.TABLE_NAME + ' SET ' + @.collist

SELECT @.sql

EXEC(@.sql)

SELECT * FROM myTable99
GO

DROP TABLE myTable99
GO|||OK,

I will take a look at your coding. I should have specified that none of the fields is a datetime. They are all money, int, real, or varchars. Thanks again for your time and diligence.

Dave|||We resolved it in-house. Here is the answer! Thanks again.

================================================== =====

DECLARE @.FieldName char (25)
DECLARE cursor_update_rpt_Scr_B0000_MiniFinancials CURSOR For

select column_name
from information_schema.columns
where table_name = 'rpt_Scr_B0000_MiniFinancials'

open cursor_update_rpt_Scr_B0000_MiniFinancials

FETCH NEXT FROM cursor_update_rpt_Scr_B0000_MiniFinancials
INTO @.FieldName

WHILE @.@.FETCH_STATUS = 0
BEGIN

execute('
update rpt_Scr_B0000_MiniFinancials
set '+@.FieldName+' = 0
where '+@.FieldName+' is null
')

FETCH NEXT FROM cursor_update_rpt_Scr_B0000_MiniFinancials
INTO @.FieldName

END

CLOSE cursor_update_rpt_Scr_B0000_MiniFinancials
DEALLOCATE cursor_update_rpt_Scr_B0000_MiniFinancials|||Well use mine anyway and blow their minds...cursors...ech

Besides mine will be faster|||WOuld setting default = 0 on the table structure accomplish what you want.|||I just tried that, one of the other guys here suggested this also. It fails our process due to the fact that there is an update statement we run to perform calculations, ie averaging. If I try to default the table values to zeros it blows up when it reaches this update with a can't divide by zero error. If the process gets to this update with NULLs it is fine as far not erroring out but these NULL fields don't get populated with zeros either. So the way it is set up I must perform a later update to make all NULLs zero. Make sense?

Davesql