使用带参数的命令时临时表的“无效的对象名称” [英] 'Invalid object name' for temporary table when using command with parameters

问题描述

我正在创建一个临时表，并使用相同的命令和连接用两个单独的语句填充该表。但是，如果我使用在创建之前插入的参数创建表，则会得到无效的对象名称。如果我在创建之后添加它，它就可以正常工作。

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.

失败：

        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

        }

工作：

        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

        }

编辑：

SQL Profiler对此提供了更多启示。

SQL Profiler shed more light on this.

如果命令具有任何参数，则基础代码将发出 exec sp_executesql。如果清除了参数，则基础代码将发布更直接的 CREATE TABLE。在sp_executesql之后清理临时表，这解释了我在这里看到的内容。

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.

对我来说，这可能是SqlCommand（或相关代码）中的错误，但是

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

推荐答案

问题实际上出在 exec sp_executesql语句中。当ADO检测到sqlCommand中声明了参数时，默认情况下使用 sp_executesql而不是 exec。但是在这种情况下，第一个命令将创建TEMPORAL表，并且众所周知，时态表仅在存储过程（sp_executesql）内部有效，并在退出时删除。因此，第二个INSERT语句在第一个示例代码中不再有效。在第二个中，成功创建了临时表，并正常执行了insert语句。希望对您有所帮助。

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.

这篇关于使用带参数的命令时临时表的“无效的对象名称”的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！