Update with Output (@MyTableVar), fails in C#, works fine in SQL Server

Question

I am trying to run a query (using C#) that updates x number records in SQL Server and where I afterwards can loop through the updated records.

The query works fine in SQL Server Management Studio, but in C# code it fails with this error:

Must declare the table variable "@MyTableVarSELECT"

Code:

string query =
       "DECLARE @MyTableVar table(ID varchar(50), Byy varchar(50))" +
       "UPDATE TOP (5) estae SET mp_used = '1'" +
       "OUTPUT inserted.mp.value1, inserted.mp_valye2 INTO @MyTableVar" +
       "SELECT * FROM @MyTableVar";

DataSet dataset = new DataSet();

using (SqlConnection conn = new SqlConnection(Properties.Settings.Default.Sql))
{
    SqlDataAdapter adapter = new SqlDataAdapter();
    adapter.SelectCommand = new SqlCommand(query, conn);

    conn.Open();
    adapter.Fill(dataset);
    conn.Close();
}

You can't use parameters in sql queries for table names in c#. — SANM2009, Jan 17 '18 at 22:18
You need to add to your cmd a parameter called MyTableVar. Do followng : SQlCommand cmd = new SqlCommand(query, conn). Then cmd.Paremter.Add() — jdweng, Jan 17 '18 at 22:18
Possible duplicate of [How can I Pass a Table Name to SqlCommand?](https://stackoverflow.com/questions/23357481/how-can-i-pass-a-table-name-to-sqlcommand) — Ňɏssa Pøngjǣrdenlarp, Jan 17 '18 at 22:51
Wow; 3 totally invalid comments; sorry, but this has nothing to do with parameterized table names — Marc Gravell, Jan 18 '18 at 00:31
@Allcam - Can you use stored Procedure here and it is very straightforward and do not need to write complex c# logic in code. — Abdul Azeez, Jan 18 '18 at 00:42
@AbdulAzeez stored procedures are also not a magic wand; the C# to invoke a stored procedure is virtually identical to the C# to invoke a correctly parameterized query - but with much easier deployment and flexibility. — Marc Gravell, Jan 18 '18 at 04:53

score 3 · Answer 1 · answered Jan 18 '18 at 00:30

The problem here is simply: whitespace.

Consider:

 string query =
        "DECLARE @MyTableVar table(ID varchar(50), Byy varchar(50))" +
        "UPDATE TOP (5) estae SET mp_used = '1'" +
        "OUTPUT inserted.mp.value1, inserted.mp_valye2 INTO @MyTableVar" +
        "SELECT * FROM @MyTableVar";

Those newlines only exist in the C# - the concatenated SQL string is all one line.

Options:

add whitespace
add semicolons
add both

For the last:

string query = @"
DECLARE @MyTableVar table(ID varchar(50), Byy varchar(50));

UPDATE TOP (5) estae SET mp_used = '1'
OUTPUT inserted.mp.value1, inserted.mp_valye2 INTO @MyTableVar;

SELECT * FROM @MyTableVar;";

This should now work. The use of @"..." (a "verbatim string literal") allows us to use newlines etc inside the C# string. The semicolons make the individual commands in the TSQL more explicit so they don't bleed into each-other.

score 0 · Answer 2 · answered Jan 17 '18 at 22:30

You can simplify the query and not cause parameter confusion by just using OUTPUT to return the result set. Also, you don't need to open or close the connection if you use a SqlDataAdapter. I have attempted to correct your typos, I don't know if I got it right.

string query = "UPDATE TOP (5) estae SET mp_used = '1' OUTPUT inserted.mp_value1, inserted.mp_value2";

DataSet dataset = new DataSet();
using (SqlConnection conn = new SqlConnection(Properties.Settings.Default.Sql))
{
    SqlDataAdapter adapter = new SqlDataAdapter();
    adapter.SelectCommand = new SqlCommand(query, conn);
    adapter.Fill(dataset);
}

Update with Output (@MyTableVar), fails in C#, works fine in SQL Server

2 Answers2