Unable to create temp table via ExecuteNonQuery with parameters

Question

It is reasonable to create a temp table via following statement

DECLARE @p0 NVARCHAR(100) = '1'

SELECT id, name INTO #TempTable FROM Table1 WHERE name = @p0

Then I state the same thing with SqlCommand.ExecuteNonQuery() without lucky.

static void Main(string[] args)
        {
            string connStr = "Server=localhost;Database=DBName;Trusted_Connection=True;";

            string createTableStatement = @"SELECT id, name INTO #TempTable FROM Table1 WHERE name = @p0";
            string queryTableStatement = "SELECT * FROM #TempTable";
            SqlConnection sqlConnection = new SqlConnection(connStr);
            SqlCommand cmd = sqlConnection.CreateCommand();
            SqlDataAdapter adapter = new SqlDataAdapter(queryTableStatement, sqlConnection);
            DataTable dt = new DataTable();
            try
            {
                sqlConnection.Open();
                cmd.CommandText = createTableStatement;
                cmd.CommandType = CommandType.Text;
                SqlParameter sqlParameter = new SqlParameter("@p0", SqlDbType.NVarChar, 100);
                sqlParameter.Value = "1";
                cmd.Parameters.Add(sqlParameter);
                cmd.ExecuteNonQuery();

                adapter.Fill(dt);
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.ToString());
            }
            finally
            {
                sqlConnection.Close();
            }
        }

cmd.ExecuteNonQuery() gives no error but the adapter.Fill(dt) hit #TempTable not existed exception. After I investigate the issue, I found it all because of parameter is added. If no parameter, ExecuteNonQuery can create table without any problem. I did a lot search but couldn't find a similar case like that. Does anyone have any sight on that?

Answer 1

Hi @Light Qian , Welcome to Microsoft Q&A,

In your case, using parameterized queries to create temporary tables seems to cause execution plan optimization issues. This may be due to SQL Server being unable to correctly generate the execution plan for the SELECT INTO statement because it has difficulty determining the parameter values.

I suggest you create a temporary table first and then use parameterized queries to insert data: First create a temporary table and then use parameterized queries to insert data into the temporary table. This is a safe approach, but requires two queries to be executed.

Similar to

string createTableStatement = "CREATE TABLE #TempTable (id INT, name NVARCHAR(100))";
string insertDataStatement = "INSERT INTO #TempTable (id, name) SELECT id, name FROM Table1 WHERE name = @p0";

// ...

using (SqlCommand createTableCmd = new SqlCommand(createTableStatement, sqlConnection))
using (SqlCommand insertDataCmd = new SqlCommand(insertDataStatement, sqlConnection))
{
    createTableCmd.CommandType = CommandType.Text;
    insertDataCmd.CommandType = CommandType.Text;

    createTableCmd.ExecuteNonQuery();

    SqlParameter sqlParameter = new SqlParameter("@p0", SqlDbType.NVarChar, 100);
    sqlParameter.Value = "1";
    insertDataCmd.Parameters.Add(sqlParameter);
    insertDataCmd.ExecuteNonQuery();
}

Best Regards,

Jiale

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment". 

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.