'Invalid object name' for temporary table when using command with parameters

Question

I'm creating a temporary table and populating it with two separate statements using the same command and connection. However, I'm getting an 'Invalid object name' if I create the table with the parameter inserted before the create. If I add it after the create, it works fine.

The temporary table is supposed to last the entire session, so I don't see what it matters when the parameter is added to the command object.

FAILS:

        using (SqlConnection conn = new SqlConnection("Data Source=.;Initial Catalog=TEST;Integrated Security=True;"))
        using (SqlCommand cmd = conn.CreateCommand())
        {
            conn.Open();

            cmd.Parameters.Add(new SqlParameter("@ID", 1234));

            cmd.CommandText = "CREATE TABLE #Test (ID INT NOT NULL PRIMARY KEY, I INT NOT NULL)";
            cmd.ExecuteNonQuery();

            cmd.CommandText = "INSERT INTO #Test VALUES (@ID, 1)";
            cmd.ExecuteNonQuery();

            ..... more code that uses the table

        }

WORKS:

        using (SqlConnection conn = new SqlConnection("Data Source=.;Initial Catalog=TEST;Integrated Security=True;"))
        using (SqlCommand cmd = conn.CreateCommand())
        {
            conn.Open();

            cmd.CommandText = "CREATE TABLE #Test (ID INT NOT NULL PRIMARY KEY, I INT NOT NULL)";
            cmd.ExecuteNonQuery();

            cmd.Parameters.Add(new SqlParameter("@ID", 1234));

            cmd.CommandText = "INSERT INTO #Test VALUES (@ID, 1)";
            cmd.ExecuteNonQuery();

            ..... more code that uses the table

        }

edit:

SQL Profiler shed more light on this.

If the command has any parameters, the underlying code is issuing an "exec sp_executesql". If the Parameters are cleared, the underlying code issues a more direct "CREATE TABLE". Temp tables are cleaned up after an sp_executesql, which explains what I'm seeing here.

To me, this would be a bug in the SqlCommand (or related) code but since I now have an explanation I can move on.

Answer 1

The problem is in fact in "exec sp_executesql" statement. When ADO detects that there are parameters declared in the sqlCommand, uses by default "sp_executesql" instead of "exec". But in this case, the first command is creating a TEMPORAL table and, as known, temporal tables are only valid inside a stored procedure (sp_executesql) and are deleted when exit. So consequently the second INSERT statement is not longer valid in the first example code. In the second one, the temporal table is created sucessfully and the insert statement is executed normally. Hope it helps.