在存储过程中连续创建和删除临时表 [英] Creating and deleting temp tables consecutively in a stored procedure

问题描述

这段代码在我运行时不起作用，我不明白为什么它不应该，错误是一个表已经存在，所以它看起来不像是一个实际的错误，而是一个错误验证过程，也许我错了.这是一个程序中的东西，所以在任何地方都不能使用 go.

This code doesn't work when I run it and I don't understand why it shouldn't, the error is that a table already exists so it doesn't seem so much as an actual bug but a bug in the verification process, perhaps I am wrong. it's something from a procedure so doesn't work to use go's everywhere.

在我的第一个问题中，我过于简单化了我的问题

in my first question I grossly oversimplified my problem

这些是我的限制:

我使用临时表在过程之间进行通信，在这种情况下，我需要使用相同的过程两次但参数不同，因此我需要两次通知表#a".因为工作方式是在执行过程之前创建一个临时表，在这种形式下，过程将知道是否需要选择或通知表.如果它通知表，它也负责创建列(这样做是为了避免过程之间的依赖关系).所以我不能重用同一个表，因为已经创建了列.

I use temporary tables to communicate between procedures, in this case I needed to use the same procedure twice but with different parameters so i needed the table "#a" to be informed twice. Because the way of working is to create a temporary table before executing the procedure and in that form the procedure will know if it needs to do a select or inform the table. If it informs the table it is also responsible to create column (this is done to avoid dependencies between procedures). so I can't reuse the same table because the columns have been create already.

我找到的解决方案是创建另一个存储过程来完成第二部分的工作，我可以更改过程 sp_a，但我没有实际控制它.

The solution I found was to create another stored procedure which will do the work for the second part, I could have changed the procedure sp_a but I don't have actual control of it.

CREATE PROCEDURE zzzzz_testtodelete
AS
BEGIN
    CREATE TABLE #a(idtmp bit)
    Exec sp_a
        @a = 1
    .....
    DROP TABLE #a

    CREATE TABLE #a(idtmp bit)
    Exec sp_a
        @a = 2
    .....
    DROP TABLE #a
END

这是正常的还是sql的bug?

Is this normal or is it a bug from sql?

推荐答案

这是正常的.这里发生的是编译错误，而不是运行时错误.这是因为在解析批处理时，SQL Server 看到您已经创建了#a，因此再次创建它会产生错误.同样，此错误不是运行时错误，而是编译错误.测试:

This is normal. What happens here is a compile error, not a run-time error. This is because when parsing the batch, SQL Server sees that you already have created #a, so creating it again will produce an error. Again, this error is not a run-time error but compile error. To test:

PRINT 'TESTING'

CREATE TABLE #a(idtmp bit)
DROP TABLE #a

CREATE TABLE #a(idtmp bit)
DROP TABLE #a

注意 TESTING 不会被打印出来，因为由于编译错误，批处理甚至没有被执行.

Note that TESTING is not printed, since the batch is not even executed due to the compile error.

在第一个 DROP 和第二个 CREATE 之间放置一个 GO 将解决问题:

Putting a GO in between the first DROP and second CREATE will fix the problem:

PRINT 'TESTING'

CREATE TABLE #a(idtmp bit)
DROP TABLE #a

GO

CREATE TABLE #a(idtmp bit)
DROP TABLE #a

这是因为您现在有两个单独的批次.SQL Server 批量解析和验证 sql 查询.

This is because you have two separate batches now. SQL Server parses and validates sql queries by batch.

只有在 OP 编辑​​后，他/她才声明这是在存储过程中使用的.在这种情况下，没有真正的解决方案.尽管如此，这是一个编译错误.代码未执行.

Only after the OP's edit that he/she states that this is used in stored procedure. In that case, there is no real solution. Still, this is a compile error. The code is not executed.

此外，根据 BOL:

如果在单个存储中创建了多个临时表程序或批处理，它们必须有不同的名称.

If more than one temporary table is created inside a single stored procedure or batch, they must have different names.

解决方法是为表使用不同的名称.或者只是在第一次使用后截断表格.

The workaround is to use different names for the tables. Or just truncate the table after the first use.

这篇关于在存储过程中连续创建和删除临时表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！