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


Comments

Anonymous said…
This comment has been removed by a blog administrator.
January 13, 2010 at 11:07 PM
Post a Comment

Popular posts from this blog

Fluent NHibernate - Incorrect syntax near the keyword 'Group'

I have to share this. I spent a good amount of time searching around Fluent NHibernate for a way to escape columns in the generated SQL while still using AutoMapping. My initial configuration looked like: Fluently.Configure() .Database(MsSqlConfiguration.MsSql2005.ConnectionString( c => c.Is(ConfigurationManager.ConnectionStrings["Something"].ConnectionString))) .Mappings(x => x.AutoMappings.Add(AutoPersistenceModel.MapEntitiesFromAssemblyOf () .WithSetup(convention => { convention.FindIdentity = p => p.Name == p.DeclaringType.Name + "Id"; convention.GetComponentColumnPrefix = type => type.Name + "Id"; } ) .BuildSessionFactory() This works great, but I had a property in my class (SomeType) that is called Group. This was causing the SqlException: Incorrect syntax near the keyword 'Group' (others would be like: Incorrect syntax near the keyword 'User' or Incorrect syntax near the keyword ...
Read more

Fluent NHibernate + Encrypting Values

I recently created two CLR User Defined Functions for SQL Server 2005 (written in C#) to decrypt & encrypt a given string (based on a hard-coded key). select dbo.EncryptString('Testing') -- Returns: Fjfds243qcm43fsdj2343== select dbo.DecryptString('Fjfds243qcm43fsdj2343==') -- Returns: Testing Pretty simple stuff. This will allow the user with enough access to pull encrypted data out of the database and provide the ability to restrict decryption to other SQL users. Now to implement this in code... Because it's awesome, Fluent NHibernate has a method called FormulasIs that assists in building out values using SQL. public class User { public virtual int Id { get; set; } public virtual DateTime DateOfBirth { get; set; } } public sealed class UserMap : ClassMap { public UserMap() { WithTable("dbo.[User]"); Id(x => x.Id, "[ID]"); Map(x => x.DateOfBirth, "DOB").FormulaIs("dbo.DecryptString(DOB)"); ...
Read more

MapReduce in C# using Task Parallel Library

Back in August I starting playing with a C# implementation of Google's MapReduce algorithm. The implementation was based on something Stephan Brenner did, although I completely refactored it. Today I added a little bit of logic to split up the actual execution of Map & Reduce in this implementation using the Task Parallel Library in .NET 4.0. Check out the source code for MapReduce in C# on GitHub. Below is an excerpt from the Tests on how to implement the library. Counting Words in Files public static List<KeyValuePair<string, int>> Map(FileInfo document, string text) { var items = text.Split('\n', ' ', '.', ',','\r'); return items.Select(item => new KeyValuePair<string, int>(item, 1)).ToList(); } public static List<int> Reduce(string word, List<int> wordCounts) { if (wordCounts == null) return null; var result = new List<int> { 0 }; foreach (var value in wordC...
Read more