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

. 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

... 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