2009-09-25

Convert literal SQL 'NULL' to real NULL

Pretty straight forward. Had to do this today & thought I'd share the solution I came up with.


/*
Will turn literal "NULL" into SQL NULL in any table (for all columns)
codesnippet:25616582-ced6-4e59-ab4c-25d2128cc4b6
*/

declare @tableName varchar(50)

---- Configuration -----
set @tableName = 'PlanImport'
------------------------


declare @i int
declare @count int
declare @sql varchar(8000)
declare @columnName varchar(8000)
declare @columns table (id int identity(1,1), columnName varchar(50))

insert into @columns
select [Name] from sys.columns where object_id = object_id(@tableName)
and system_type_id in (select system_type_id from sys.types where [name] like '%varchar%' or [name] like '%text%' )

set @i = 1
set @count = (select max(id) from @columns)

while(@i <= @count)
begin
set @columnName = (SELECT [columnName] FROM @columns WHERE [ID] = @i)
set @sql = 'UPDATE [' + @tableName + '] SET [' + @columnName + '] = NULL WHERE [' + @columnName + '] = ''NULL'''

EXECUTE(@sql)

set @i = @i + 1
end


1 comment:

tingting said...
This comment has been removed by a blog administrator.