a SP to strip nulls from a table I think I'm close, but the apostrophes are killing me:

    create procedure denull (@database varchar(255), @table varchar(255))

    as

    declare @sql nvarchar(max)

    set @sql = ' declare @name varchar(255), @column varchar(255))

    declare c cursor local for 

    select st.name, sc.name from ' + @database + '.sys.tables st
    inner join sys.columns sc on st.object_id = sc.object_id
    where st.name = ''' + @table + '''

    update ''''@name'''' set ''''@column'''' = '''' where ''''@column is null''



    fetch next from st.name, sc.name

    close c

    deallocate c
    '
    print @sql

    exec sp_executesql @sql


it prints out to:

     declare @name varchar(255), @column varchar(255))

    declare c cursor local for 

    select st.name, sc.name from livendbmirror.sys.tables st
    inner join sys.columns sc on st.object_id = sc.object_id
    where st.name = 'CMS_27_Psych_results'

    update ''@name'' set ''@column'' = '' where ''@column is null'



    fetch next from st.name, sc.name

    close c

    deallocate c
    Msg 102, Level 15, State 1, Line 1
    Incorrect syntax near ')'.
    Msg 102, Level 15, State 1, Line 9
    Incorrect syntax near ''.
    Msg 105, Level 15, State 1, Line 9
    Unclosed quotation mark after the character string '



    fetch next from st.name, sc.name

    close c

    deallocate c
    '.


Question: How can I dynamically remove null values and replace them with '' for fussy end users?
I'd think twice about carte-blanche modifying data the way you are proposing unless you are doing this one-time as part of a data-purity effort.

I'd write code to do the modification using non-dynamic T-SQL first, then convert that into dynamic T-SQL, and finally add it to a stored procedure for easy re-use.

For instance, we'll create a table in tempdb, then update a column that has null values:

USE tempdb;

DROP TABLE dbo.test_table;
CREATE TABLE dbo.test_table (i varchar(3) null);
INSERT INTO dbo.test_table(i) VALUES (null)

UPDATE [dbo].[test_table]
SET [i] = '' 
WHERE [i] IS NULL;


Next, we'll add this code to a framework that uses dynamic T-SQL so we don't need to write individual statements for every column we want to update:

DECLARE @cmd nvarchar(max);
DECLARE @schema sysname;
DECLARE @table sysname;
DECLARE @column sysname;
DECLARE cur CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY
FOR
SELECT s.name
    , t.name
    , c.name
FROM sys.schemas s
    INNER JOIN sys.tables t ON s.schema_id = t.schema_id
    INNER JOIN sys.columns c ON t.object_id = c.object_id
    INNER JOIN sys.types ty ON c.system_type_id = ty.system_type_id
WHERE c.is_nullable = 1
    AND ty.name IN (
          'varchar'
        , 'char'
        , 'nvarchar'
        , 'nchar'
    )
    AND s.name = 'dbo'
    AND t.name = 'test_table'
ORDER BY s.name
    , t.name
    , c.name;

OPEN cur;
FETCH NEXT FROM cur INTO @schema, @table, @column;
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @cmd = 'UPDATE ' + QUOTENAME(@schema) + '.' + QUOTENAME(@table) + '
SET ' + QUOTENAME(@column) + ' = '''' 
WHERE ' + QUOTENAME(@column) + ' IS NULL;';
    PRINT @cmd;
    FETCH NEXT FROM cur INTO @schema, @table, @column;
END
CLOSE cur;
DEALLOCATE cur;


It uses a cursor to iterate over all the varchar, nvarchar, char, and nchar columns in the given table where the column value is null.

Next, we'll create a stored procedure to run the code.  The stored proc has a paramter, @debug, which allows us to see the UPDATE statement, or to actually run the UPDATE statement.

IF OBJECT_ID(N'dbo.RemoveNulls', N'P') IS NOT NULL
DROP PROCEDURE dbo.RemoveNulls;
GO
CREATE PROCEDURE dbo.RemoveNulls
(
    @schema_to_change sysname
    , @table_to_change sysname
    , @debug bit = 1
)
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @cmd nvarchar(max);
    DECLARE @schema sysname;
    DECLARE @table sysname;
    DECLARE @column sysname;
    DECLARE cur CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY
    FOR
    SELECT s.name
        , t.name
        , c.name
    FROM sys.schemas s
        INNER JOIN sys.tables t ON s.schema_id = t.schema_id
        INNER JOIN sys.columns c ON t.object_id = c.object_id
        INNER JOIN sys.types ty ON c.system_type_id = ty.system_type_id
    WHERE c.is_nullable = 1
        AND ty.name IN (
              'varchar'
            , 'char'
            , 'nvarchar'
            , 'nchar'
        )
        AND s.name = @schema_to_change
        AND t.name = @table_to_change
    ORDER BY s.name
        , t.name
        , c.name;

    OPEN cur;
    FETCH NEXT FROM cur INTO @schema, @table, @column;
    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @cmd = 'UPDATE ' + QUOTENAME(@schema) + '.' + QUOTENAME(@table) + '
SET ' + QUOTENAME(@column) + ' = '''' 
WHERE ' + QUOTENAME(@column) + ' IS NULL;';
        PRINT @cmd;
        IF @debug = 0
        BEGIN
            EXEC sys.sp_executesql @cmd;
        END
        FETCH NEXT FROM cur INTO @schema, @table, @column;
    END
    CLOSE cur;
    DEALLOCATE cur;
END
GO


Run the stored procedure like this, which won't actually modify data since we've left the @debug parameter set to its default value.:

EXEC dbo.RemoveNulls @schema_to_change = 'dbo'
    , @table_to_change = 'test_table';


If we want to actually modify the data, run the stored procedure like this:

EXEC dbo.RemoveNulls @schema_to_change = 'dbo'
    , @table_to_change = 'test_table'
    , @debug = 0;