why do i get error Must declare the scalar variable "@x". while the query works in the sql window?

Question

the error that I'm getting is " Must declare the scalar variable "@x"."

I'm trying to declare a variable @x and make him a NVARCHAR(50)

then id like to subtract an amount from it called @z

and finally update a Table in the server called Stock with the out come of @x -@z

        cmd.CommandText = "DECLARE @x AS NVARCHAR(50)";
        cmd.ExecuteScalar();

        for(int i=0;i<length;i++)
        {
            for(int j=0;j<2;j++)
            {
                //for ease of use
                string name = dt.Rows[i][0].ToString();
                string amount = dt.Rows[i][1].ToString();
                
                cmd.CommandText = "SELECT @x = amount FROM Stock WHERE name = '"+name +"'";
                cmd.ExecuteScalar();// im getting the error here but assume it will be in every executescalar
                Thread.Sleep(100);
                Console.WriteLine("end update");
                cmd.CommandText = "DECLARE @z AS integer";
                cmd.ExecuteScalar();
                Thread.Sleep(100);
                Console.WriteLine("end update");
                cmd.CommandText = "SELECT @z = " + amount;
                cmd.ExecuteScalar();
                Thread.Sleep(100);
                Console.WriteLine("end update");
                cmd.CommandText = "UPDATE stock SET amount = @x -@z WHERE name='" + name + "'";
                cmd.ExecuteScalar();
                Thread.Sleep(100);
                Console.WriteLine("end update");
            }
        }

dt rows is fine and does give appropriate values

Answer 1

You have a number of issues with your code:

• Primarily, you cannot declare a variable in one SQL batch then use it another, it must be the same batch.
• Do not concatenate data into your queries, this is dangerous and can cause injection attacks and syntax errors. Use parameters instead.
• SELECT @x = amount makes no sense: if you wanted to retrieve data using a parameter then you need to declare it, and if you wanted to retrieve using ExecuteScalar then you would need just SELECT amount.
• Either way, you don't need SELECT something UPDATE something just do the calculation in one UPDATE statement.
• It's unclear what the second for loop is supposed to do, it's just doing the same thing three times.
• What do you hope to achieve with Thread.Sleep?

So your code becomes:

const string query = @"
UPDATE stock
SET amount = amount - @amount
WHERE name = @name;
";

using (var conn = new SqlConnection(YourConnectionString))
using (var cmd = new SqlCommand(query, conn))
{
    // add parameters with no values
    cmd.Parameters.Add("@name", SqlDbType.VarChar, 100);  // select correct types and lengths
    cmd.Parameters.Add("@amount", SqlDbType.Decimal);
    conn.Open();

    foreach (DataRow row in dt.Rows)
    {
        // overwrite the values here
        cmd.Parameters["@name"].Value = row[0];
        cmd.Parameters["@amount"].Value = row[1];
        cmd.ExecuteNonQuery();
    }
}

However, this is still going to be slow for a large number of rows. It will be more efficient to use a Table-Valued Parameter to do the whole update in bulk.

First, create a Table Type. I usually have a few standard ones, for example a two-column one of strings and decimals:

CREATE TYPE dbo.StringDecimal AS TABLE (
  String varchar(100) PRIMARY KEY,
  Number decimal(18,9)
);

Then you can use it like this:

const string query = @"
UPDATE s
SET amount = s.amount - t.Number
FROM stock s
JOIN @tmp t ON t.String = s.name;
";

using (var conn = new SqlConnection(YourConnectionString))
using (var cmd = new SqlCommand(query, conn))
{
    cmd.Parameters.Add(new SqlParameter("@tmp", SqlDbType.Structured) {
        Value = dt,
        TypeName = "dbo.StringDecimal",
    });
    conn.Open();
    cmd.ExecuteNonQuery();
}

The value dt must have exactly two columns in the correct order.