"Null" to "blank" Stored Procedure
Moderator: General Moderators
"Null" to "blank" Stored Procedure
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??
feyd | Please use
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]
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 mycursorfeyd | 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
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.
-
fractalvibes
- Forum Contributor
- Posts: 335
- Joined: Thu Sep 26, 2002 6:14 pm
- Location: Waco, Texas
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
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