How do I get around this common SQL problem

Question

Haven't come across this in ages and when I searched for the solution I couldn't find one. I think its called overloading in SQL. Basically when I have "" (an empty string) for any parameter in this SQL I don't want to set a value in the database...

NOTE: I want to do it at a SQL level not do it at a C# level because its sloppy that way.

string Sql = "IF NOT EXISTS (SELECT * FROM tbl_FileSystemReferences) "
            + "INSERT INTO tbl_FileSystemReferences (UploadDir) VALUES (null) "
            + "UPDATE tbl_FileSystemReferences SET "
            + "UploadDir=@UploadDir, "
            + "ThumbnailDir=@ThumbnailDir, "
            + "ArchiveDir=@ArchiveDir, "
            + "RealDir=@RealDir, "
            + "FlashDir=@FlashDir, "
            + "AssociatedFilesDir=@AssociatedFilesDir, "
            + "EnableArchiving=@EnableArchiving, "
            + "AppWideDir=@AppWideDir, "
            + "FFmpegDir=@FFmpegDir, "
            + "InstallationDir=@InstallationDir ";

SqlCommand Command = new SqlCommand(Sql);
Command.Parameters.AddWithValue("@UploadDir", f.UploadDir);
Command.Parameters.AddWithValue("@ThumbnailDir", f.ThumbnailDir);
Command.Parameters.AddWithValue("@ArchiveDir", f.ArchiveDir);
Command.Parameters.AddWithValue("@RealDir", f.RealDir);
Command.Parameters.AddWithValue("@FlashDir", f.FlashDir);
Command.Parameters.AddWithValue("@AssociatedFilesDir", f.AssociatedFilesDir);
Command.Parameters.AddWithValue("@EnableArchiving", f.EnableArchiving);
Command.Parameters.AddWithValue("@AppWideDir", f.AppWideDir);
Command.Parameters.AddWithValue("@FFmpegDir", f.FFmpegDir);
Command.Parameters.AddWithValue("@InstallationDir", f.InstallationDir);

ExecuteNonQuery(Command);

I know there is a way I used to do this with stored procedure I just cant remember how (I think it's called overloading)....

Cheers,

Answer 1

one way would be on a stored procedure, where you would receive all those parameters, then before the query either:

• you allow to pass null

• you convert each parameter to null if they are empty as:

  select @UploadDir = null where @UploadDir = ''

you would do that for all your parameters, then on update query:

IF NOT EXISTS (SELECT * FROM tbl_FileSystemReferences)
INSERT INTO tbl_FileSystemReferences (UploadDir) VALUES (null)
UPDATE tbl_FileSystemReferences SET
UploadDir=coalesce(@UploadDir, UploadDir),
ThumbnailDir=coalesce(@ThumbnailDir, ThumbnailDir),
ArchiveDir=coalesce(@ArchiveDir, ArchiveDir),
RealDir=coalesce(@RealDir, RealDir),
FlashDir=coalesce(@FlashDir, FlashDir),
AssociatedFilesDir=coalesce(@AssociatedFilesDir, AssociatedFilesDir),
EnableArchiving=coalesce(@EnableArchiving, EnableArchiving),
AppWideDir=coalesce(@AppWideDir, AppWideDir),
FFmpegDir=coalesce(@FFmpegDir, FFmpegDir),
InstallationDir=coalesce(@InstallationDir, InstallationDir)