在存储过程中连续创建和删除临时表

问题描述

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

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

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

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

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