Converting all blanks to NULL in a SQL table

The bane of my existence, blank values that should be NULL in a database. This happens frequently when importing Excel/Flat files particularly within SQL Server. Running hundreds of UPDATE statements isn’t fun, so I use a Stored Procedure with a CURSOR to take care of it.

CREATE PROCEDURE [dbo].[usp_getRidOfTableBlanks](@tableName nvarchar(50))
    AS

    DECLARE @colName varchar(255)
    DECLARE @sql varchar(4000)
    DECLARE Table_Cursor CURSOR FOR

    select COLUMN_NAME
    from INFORMATION_SCHEMA.COLUMNS
    where TABLE_NAME=@tableName
    OPEN Table_Cursor;
    FETCH NEXT FROM Table_Cursor INTO @colName
	
    WHILE @@FETCH_STATUS = 0
       BEGIN
          set @sql = 'UPDATE ' + @tableName + ' SET [' + @colName + '] = NULL WHERE [' + @colName + '] = '''';';
                print(@sql)
                exec (@sql)
          FETCH NEXT FROM Table_Cursor INTO @colName
       END;
    CLOSE Table_Cursor;
    DEALLOCATE Table_Cursor;
GO

Just run an exec with the table name and voila, beautiful UPDATE statements while you sit back and wish this didn’t happen.

exec usp_getRidOfTableBlanks 'MyTable'