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

please use ***parameterised queries*** - building SQL queries by concatenation etc. is a recipe for disaster. not only is it a source for many hard to debug syntax errors, it's also a wide, open gate for ***[SQL Injection attacks](https://bobby-tables.com/)***. — Franz Gleichmann, Apr 23 '22 at 16:19
@FranzGleichmann I've looked up parameterized queries online and i unfortunately do not understand how to convert my code, how would you go about it? — roee oselka, Apr 23 '22 at 16:30
Use a parameter for the `name` variable. There are many examples online if you google "how to use a parameterized query in c#". For example [the first result](https://csharp-station.com/Tutorial/AdoDotNet/Lesson06) gives an example that's nearly identical to yours. — Rufus L, Apr 23 '22 at 19:37
Your variable won't carry over from one query to the next. Declare it using c# instead, or do it all in one query script — Caius Jard, Apr 23 '22 at 19:49
SQL variables are scoped to the one batch; each `.execute` is a new batch. — Stu, Apr 23 '22 at 22:16

score 1 · Answer 1 · answered Apr 24 '22 at 12:09

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.

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

1 Answers1