Trying to update all string values of 'NULL' to NULL in sql server

Question

I have a database table that was imported from a csv file which had NULL in it, so that when it was imported instead of fields being NULL they contain the string value 'NULL'.

The CSV file is to big to open in a text editor to edit out all of the NULL so I am trying to create a SQL query to update each column of the table.

So far I have this

Alter PROCEDURE [dbo].[sp_fixnulls]
     @column nvarchar(100) 
AS
BEGIN
     SET NOCOUNT ON;
     UPDATE my_table
     SET @column=''
     WHERE @column = 'NULL'
END 

---------------

sp_fixnulls (SELECT column_name FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME  ='my_table')

but this is not working. I get the error message

Msg 201, Level 16, State 4, Procedure sp_fixnulls, Line 0
Procedure or function 'sp_fixnulls' expects parameter '@column', which was not supplied.

Answer 1

@column is a variable. It cannot dynamically swap out its contents into SQL. You need to use TSQL to accomplish this, whereby you generate the SQL into a string then execute it. Such as:

DECLARE @sql VARCHAR(MAX)
SET @sql = '
UPDATE my_table
SET ' + @column + '=''''
WHERE ' + @column + ' = ''NULL''
'

EXEC (@sql) -- don't forget the parentheses

A small note that my code sets the column to empty string, which is NOT the same as NULL. I went with your existing example. If you want NULL, then

SET ' + @column + '= NULL

Answer 2

'Msg 201, Level 16, State 4, Procedure sp_fixnulls, Line 0 Procedure or function 'sp_fixnulls' expects parameter '@column', which was not supplied.'

The error you're getting is because you're trying to pass a select statement as a parameter to a stored procedure. If you were to pass just the name of one column to the stored procedure, you would get past that error.

Then you wouldn't get another error, but you wouldn't get the result you want. If you fix your stored procedure as Eli recommended (with the addition of parentheses around @sql in the EXEC statement, as I commented), it will work.

Then you'll need to wrap your stored procedure with a cursor (which most people don't recommend, but works when needed). Alternatively, you can just select all of the column names and generate a bunch of execute statements, and then run the statements.

SELECT 'EXECUTE sp_fixnulls N''' + column_name + ''';'
        FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'my_table';

This will generate statements like the following (assuming the table has columns id, name, and date).

EXECUTE sp_fixnulls N'id';
EXECUTE sp_fixnulls N'name';
EXECUTE sp_fixnulls N'date';

Another option is to forgo the stored procedure altogether.

SELECT 'UPDATE my_table SET ' + column_name
        + ' = NULL WHERE ' + column_name + ' = ''NULL'';'
        FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'my_table';

This will generate statements like the following.

UPDATE my_table SET id = NULL WHERE id = 'NULL';
UPDATE my_table SET name = NULL WHERE name = 'NULL';
UPDATE my_table SET date = NULL WHERE date = 'NULL';