Unable to create temp table via ExecuteNonQuery with parameters

Light Qian 20 Reputation points
2023-10-20T02:35:12.6166667+00:00

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?

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,967 questions
C#
C#
An object-oriented and type-safe programming language that has its roots in the C family of languages and includes support for component-oriented programming.
11,005 questions
0 comments No comments
{count} votes

Accepted answer
  1. Jiale Xue - MSFT 46,296 Reputation points Microsoft Vendor
    2023-10-20T03:43:50.77+00:00

    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.


0 additional answers

Sort by: Most helpful

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.