Can a ADO.NET SQL command parameter contain a sub-query?

Question

Is it possible to create a SQL select max(id) as a variable inside a query?

This doesn't work:

command.CommandText = "INSERT INTO ordreid (ordrenr,ordreid) SELECT @ordrenr, @ordreid";
command.Parameters.AddWithValue("@ordrenr", nyordre);
command.Parameters.AddWithValue("@ordreid", ("MAX(ordreid)+1 FROM ordreid"));
command.ExecuteScalar();

Here is a photo of what I'd like to do. 5 customers have added items to their orders. Customer 1 has 3 items, customer 2 has 4 items, customer 3 has 1 item, and so on...

This way I have 1 ordreid even through the order could consist of 30 items.

Answer 1

Query parameters cannot include SQL verbatim.

This is how such prepared statements prevent SQL Injection as they are not directly inserted. Rather only the corresponding data is used in the query - in this case that is the string that contains SQL, and results in invalid SQL syntax.

The SQL text then needs to look similar to the following, although this probably does not do what is desired. (Asking how to do the higher level task will lead to actually useful queries/approaches.)

@"INSERT INTO ordreid (ordrenr, ordreid)
  SELECT @ordrenr, MAX(ordreid)+1
  FROM ordreid"

Answer 2

command.Parameters.AddWithValue("@ordreid", ("MAX(ordreid)+1 FROM ordreid"));

The method name AddWithValue hints at the reason why this won't work: Parameters contain data, that is: values. They cannot represent a part of a SQL statement.

Your intention appears to be that @ordreid should be replaced with that piece of SQL. If this is so, then there's no reason to even have a parameter. Simply perform the substitution manually and change the CommandText:

command.CommandText = @"INSERT INTO ordreid (ordrenr,  ordreid) 
                        VALUES (@ordrenr, (SELECT MAX(ordreid)+1 FROM ordreid));";

Note that I changed four things (apart from spreading the command text across two lines, for legibility's sake, using C#'s @"…" string syntax). Only the first two points are crucial:

1. I moved the MAX(…) SQL directly into your CommandText. This makes the (invalid) @ordreid parameter obsolete.

2. To determine the value of MAX(ordreid) FROM ordreid, you need a sub-query; thus the added SELECT before MAX. Otherwise, the syntax wouldn't be valid.

3. I replaced your SELECT with a VALUES table value constructor. (Otherwise, because of the previous point, we'd have two SELECTs very close to each other, which would look somewhat confusing.)

4. I added a ; at the end of your query. Current versions of SQL Server don't yet require such a statement terminator, but Microsoft has hinted that they might become compulsory in future versions of T-SQL. I therefore believe that it's a good habit to get into now.

That all being said, you should probably turn the ordreid column into an IDENTITY column and let SQL Server choose the value to be inserted (making the SELECT MAX(…) business obsolete). Otherwise, if two processes or threads execute the same INSERT command at the same time, you might end up with several rows having the same value for ordreid.