Quickly Replace Blank Values with NULLs in Entire Table

When importing flat-file data, a lot of processes don't necessarily allow for automatic use of a NULL in place of blank ('') values. I needed a quick way to update every single blank value to a NULL to clean up the data post-import in these situations. I found a very easy code snippet to pull the dynamic update statements necessary, but even this little bit of extra copy-paste-execute work seemed tedious after 10+ times.

Code from StackOverflow poster Robert N: (http://stackoverflow.com/a/19057017/3015030)

SELECT ('UPDATE '+@tablename+' SET ['+name+'] = NULL WHERE ['+name + '] = '''';')
FROM syscolumns
WHERE id = OBJECT_ID(@tablename)
AND isnullable = 1;

My addition to this was to automate the whole thing into a stored procedure. This SPROC accepts a fully qualified table name (or locally qualified name) and creates the statements, then passes to a cursor to execute each. I created it in my [master] database so that I could use it across DBs.

 USE [master] GO
 SET ANSI_NULLS ON GO
 SET QUOTED_IDENTIFIER ON GO
 -- =============================================
 -- Author: LikeableBias (http://blog.brycebaker.net)
 -- Create date: 2016-06-27
 -- Description: Finds and NULLs all blank values in table where column allows nulls
 -- =============================================
CREATE PROCEDURE [dbo].[sproc_NullBlanks] 
 @tablename NVARCHAR(MAX)
AS
 BEGIN
 SET NOCOUNT ON;
 
--------Insert update statements to temp table for execution
 DECLARE @statements TABLE (statement NVARCHAR(MAX))
 INSERT INTO @statements (statement)
 SELECT ('UPDATE '+@tablename+' SET ['+name+'] = NULL WHERE ['+name+'] = '''';')
 FROM syscolumns
 WHERE id = OBJECT_ID(@tablename)
 AND isnullable = 1;
 
--------Open cursor, execute statements, then close cursor
DECLARE @statement NVARCHAR(MAX)
DECLARE cur CURSOR LOCAL FOR
 SELECT statement FROM @statements
 
OPEN cur
FETCH NEXT FROM cur INTO @statement
 
WHILE @@FETCH_STATUS = 0 BEGIN
 EXEC sys.sp_executesql @statement
 FETCH NEXT FROM cur INTO @statement
END
 
CLOSE cur
DEALLOCATE cur
 
END
GO

Once created, you can execute the SPROC and pass in the variable as you would any other:

exec dbo.sproc_NullBlanks @tablename = '[dbname].[dbo].[tablename]'

You could, of course, just pull out the code and execute as a snippet instead of creating an entire SPROC instead.