"Null" to "blank" Stored Procedure

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
erupt
Forum Commoner
Posts: 58
Joined: Sat Feb 25, 2006 10:24 am

"Null" to "blank" Stored Procedure

Post 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??
erupt
Forum Commoner
Posts: 58
Joined: Sat Feb 25, 2006 10:24 am

Post 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]
fractalvibes
Forum Contributor
Posts: 335
Joined: Thu Sep 26, 2002 6:14 pm
Location: Waco, Texas

Post 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
erupt
Forum Commoner
Posts: 58
Joined: Sat Feb 25, 2006 10:24 am

Post 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.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Couldn't the table structure be altered such that NULL isn't allowed?
erupt
Forum Commoner
Posts: 58
Joined: Sat Feb 25, 2006 10:24 am

Post 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.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.
fractalvibes
Forum Contributor
Posts: 335
Joined: Thu Sep 26, 2002 6:14 pm
Location: Waco, Texas

Post 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
Post Reply