SQL CASE statement WHEN and THEN values from SqlParameterCollection-CodePudding

I am not sure if this question already has answers on SO, but as I couldn't find one, I am writing here. I am trying to write an update query using CASE statement. The values of WHEN and THEN comes from user-given data where I need to update multiple rows with multiple values that the user gives. I am eliminating the loop that I have written in the original code in the snippet below as the problem is only with AddWithValue. Is there a way to use SqlParameterCollection to assign the THEN value? Or is a simple string concatenation a way to do this?

SqlCommand command = new SqlCommand("UPDATE [Table] SET Column = CASE ", connection);
command.CommandText  = "WHEN Column = @Column1 THEN @Column2";
command.CommandText  = "ELSE Column END";
command.Parameters.AddWithValue("@Column1", "Value1");
command.Parameters.AddWithValue("@Column2", "Value2");
connection.Open();
command.ExecuteNonQuery();

The error:

Incorrect syntax near '@Column2'

CodePudding user response：

So finally it's all about the syntax errors as mentioned by @marc_s and @serg in the comments. This worked:

SqlCommand command = new SqlCommand("UPDATE [Table] SET Column = CASE ", connection);
command.CommandText  = "WHEN Column = @Column1 THEN @Column2 ";
command.CommandText  = "ELSE Column END";
command.Parameters.AddWithValue("@Column1", "Value1");
command.Parameters.AddWithValue("@Column2", "Value2");
connection.Open();
command.ExecuteNonQuery();

CodePudding user response：

Just wanted to note that this syntax error probably also wouldn't have occurred if you'd used verbatim strings:

    new SqlCommand(@"
UPDATE [Table] 
SET 
  Column = CASE 
    WHEN Column = @Column1 THEN @Column2 
    ELSE Column 
  END"
    , connection);

I tend to stick all the sql so it starts from indent level 0, and using the @ string means it can be formatted how I like an sql to be without any interim c# making it messy.

If you're building a variable number of cases, string interpolation might also tidy things up and you can have an interpolated @ string by prefixing $@ - I remember which way round they go by "Microsoft are polite and didn't want to fill people's code with @$ (ass)"

var whens = "WHEN...";
for(...){
  whens  = ...
  parameters.Add(..)
}

new SqlCommand(@"
UPDATE [Table] 
SET 
  Column = CASE 
    {whens}
    ELSE Column 
  END
", connection);

And of course final debugging tip; if you build an sql dynamically and you get a syntax error, pause in the debugger and look at the command text just before you run it; point to the Command variable, open the tooltip, click the magnifying glass next to CommandText property to see the string as is in a notepad style window, with new lines etc as actual new lines not as \n that the debug tooltip shows - it's a lot easier to spot syntax errors in this visualizer

enter image description here

_{image courtesy of https://gunnarpeipman.com/ef-core-toquerystring/amp/}

CodePudding user response：

try following SQL Server code:

SqlCommand command = new SqlCommand();
command.Connection = connection;
command.Parameters.AddWithValue("@Column1", "Value1");
command.Parameters.AddWithValue("@Column2", "Value2");

String StrSqlCommand = "UPDATE [Table] SET Column = CASE ";
StrSqlCommand  = " WHEN Column = @Column1 THEN @Column2 ";
StrSqlCommand  = " ELSE Column END ";

command.CommandText = StrSqlCommand;    

connection.Open();
command.ExecuteNonQuery();

Also be careful about the terms "Value1" and "Value2". Because in the code you send, the above values are considered exactly the same as the string type. If you want another value, it is better to define a variable and put it as a parameter in the above expressions. Something like this:

        String strValue1 = "Name1";
        String steValue2 = "Name2";
        SqlCommand command = new SqlCommand();
        command.Connection = connection;
        command.Parameters.AddWithValue("@Column1", strValue1);
        command.Parameters.AddWithValue("@Column2", strValue2);

        String StrSqlCommand = "UPDATE [Table] SET Column = CASE ";
        StrSqlCommand  = " WHEN Column = @Column1 THEN @Column2 ";
        StrSqlCommand  = " ELSE Column END ";

        command.CommandText = StrSqlCommand;

        connection.Open();
        command.ExecuteNonQuery();