Page 1 of 1

"Null" to "blank" Stored Procedure

Posted: Mon Nov 20, 2006 9:24 am
by erupt
Just getting into stored procedures at work and still learning ..... I need to write a stored procedure in SQL Server 2005 that will accept a table as a parameter and change all the "nulls" to blanks. Anyone have any input to help??

Posted: Mon Nov 20, 2006 11:02 am
by erupt
feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]


I have been working on this stored procedure to remove NULLS and replace them with blanks in the database.  It accepts a table as a parameter and anywhere there is a null, it is replaced with a blank.  But the cursor is screwing me up here, can anyone help me with this and tell me what the problem is??  I'm so close, yet so far.


[syntax="sql"]ALTER PROC [dbo].SP_REMOVE_NULLS

 @table varchar(100),
 @column varchar(100)
 

AS

set QUOTED_IDENTIFIER ON

declare @sql nvarchar(1000)

declare mycursor cursor for
set @sql = 
"update @table 
set @column = isnull(ltrim(rtrim(@column)), '')"


OPEN mycursor
FETCH NEXT FROM mycursor 
INTO @column
WHILE @@FETCH_STATUS = 0
BEGIN

exec ("exec SP_REMOVE_NULLS " + "'" + @column + "','" + @table + "'")

 FETCH NEXT FROM mycursor 
	INTO @column
	END
	CLOSE mycursor
DEALLOCATE mycursor

feyd | Please use[/syntax]

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]

Posted: Mon Nov 20, 2006 9:11 pm
by fractalvibes
First off, why do you need a cursor? Just issue your update.
An update doesn't return any rows to iterate through, it simply performs the update according to the conditions you specify.

fv

Posted: Tue Nov 21, 2006 8:33 am
by erupt
Cuz i need it to accept a table as a parameter when i execute the stored procedure and change every "null" in the table to a blank, regardless of what column that it is in. So that's why i was using the cursor, so it'll loop through and change all nulls to blanks until there are no more nulls left in that particular table.

Posted: Tue Nov 21, 2006 8:41 am
by feyd
Couldn't the table structure be altered such that NULL isn't allowed?

Posted: Tue Nov 21, 2006 9:01 am
by erupt
Yes very much so :D . But the problem is, there are many tables that have already been created awhile back in this particular database (they're not my databases, it's my company's dbs :x ... So there are many tables that have nulls in their records and they need to be changed. That's why i need this SP.

Posted: Tue Nov 21, 2006 4:31 pm
by feyd
That I remember, changing a table's structure from allowing NULL to not would automatically convert all the NULL entries to empty types of whatever each field holds.

Posted: Tue Nov 21, 2006 7:11 pm
by fractalvibes
Well,

I am not too familiar with SQL Server SPs, being in the DB2 world, but I don't see how that cursor you defined gives you anything to iterate through.....thinking that should be nested inside another cursor
as in:
table name passed as parameter to SP

Define cursor to hit system catalogs and return columns where table name = passed in value
begin
iterate though the column names
for each column name run your update sql
end

or something to that effect....

fv