CODEWITHSUNDEEP
c#sql-server
Shingala94
Shingala94

Reputation: 404

SQLParameter for 'Unchanged' in update query

Is it possible to add an 'Unchanged' parameter to an update query.

In some old code I have somthing like:

string query = @"Update Table SET Field1=@var1, Field2=@var2, Field3=@var3 etc..."
command.Parameters.AddWithValue("@var1", var1);
command.Parameters.AddWithValue("@var2", var2);
command.Parameters.AddWithValue("@var3", var3);

Now something has changed making Field2 var2 in input. If the user does not provide var2 Field2 should be left unchanged.

Is there a way to achieve something like this

command.Parameters.AddWithValue("@var2", var2 ?? UNCHANGED);

Or is the only option to not add the SET Field2 if var2 is not provided?

Upvotes: 1

Views: 93

Answers (3)

user1023602
user1023602

Reputation:

Something like this would do the trick.

protected string SetField(SqlCommand cmd, string field, string paramName, object value)
{
    if (value == null)
        return null;

    cmd.Parameters.AddWithValue(paramName, value);

    return string.Format("{0}={1}", field, paramName);
}

and to use it

string query = @"Update Table SET ";

IEnumerable<string> assignments = new string[]
{
    SetField(command, "Field1", "@var1", var1),
    SetField(command, "Field2", "@var2", var2),
    SetField(command, "Field3", "@var3", var3),
};

assignments = assignments.Where(s => s != null).ToList();

if (!assignments.Any())
    return;              // no update needed

query += string.Join(",", assignments);

Upvotes: 0

paparazzo
paparazzo

Reputation: 45106

In C# you need to address a couple things

This syntax is not valid 

cmd.Parameters.AddWithValue("@val1", var1 ?? DBNull.Value);

So need if else 

string query = @"Update Table  
                 SET Field1 = isnull(@var1, Field1)   
                   , Field2 = isnull(@var2, Field2)";

if(var1 == null)
    cmd.Parameters.AddWithValue("@var1", DBNull.Value);
else
    cmd.Parameters.AddWithValue("@var1", var1);

Personally if I had to do the if else then I would just not include it in the set.

Someone may have a slicker solution.

Upvotes: 1

Thom A
Thom A

Reputation: 95949

Set your parameters to be be nullable, and use ISNULL. As a very simple example:

CREATE PROC UpdateProc @ID int, @Column1 varchar(10) = NULL, @Column2 varchar(10) = NULL AS

    UPDATE YourTable
    SET Column1 = ISNULL(@Column1, Column1),
        Column2 = ISNULL(@Column2, Column2)
    WHERE ID = @ID;
GO

EXEC UpdateProc @ID = 1, @Column1 = 'Test';
--OR
EXEC UpdateProc @ID = 2, @Column1 = NULL, @Column2 = 'Test';

Upvotes: 2

Related Questions