为什么 SQL Server 认为临时表已经存在，但实际上并不存在? [英] Why does SQL Server thinks a Temp Table already exists when it doesn't?

问题描述

背景:有一个存储过程，它使用给定名称的临时表进行填充".该过程是通用的，因为它检查临时表的架构，然后根据架构执行不同的内容".我知道这有点奇怪，但我不愿意改变它，因为它在大多数情况下都可以正常工作，除了......

Background: There's a stored procedure which does "stuff" with a temp table of a given name. The procedure is generic in that it inspects the schema of the temp table and then does different "stuff" depending on the schema. I understand that this is a little weird but I'm reluctant to change it because it all works fine in most situations, except....

如果我有一个存储过程，它为同名的临时表创建两个不同的模式.从逻辑上讲，它仅根据 IF 的哪个分支创建一个临时表.问题在于，当 SQL Server 检查 Sproc 时，它似乎正在评估 IF 的两侧(如果它正在检查 SQL 语法，这很有意义.)

If I have a stored procedure which creates two different schemas for a temp table with the same name. Logically it only creates one temp table depending on which branch of the IF. The problem is that when the Sproc is checked by SQL Server it seems like it is evaluating both sides of the IF (which makes sense if it's checking the SQL syntax.)

所以这个 SQL 失败了:

So this SQL fails:

IF (1=1)
BEGIN
    CREATE TABLE #test
    (
        a BIGINT NOT NULL,
        b BIGINT NOT NULL
    )
END 
ELSE
BEGIN
    CREATE TABLE #test
    (
        a BIGINT NOT NULL,
        b BIGINT NOT NULL,
        c BIGINT NOT NULL   
    )   
END

--exec SomeProcedureWhichDoesStuffWith#Test

DROP TABLE #test 

出现以下错误:

消息 2714，级别 16，状态 1，第 14 行
已经有一个名为的对象'#test' 在数据库中.

Msg 2714, Level 16, State 1, Line 14
There is already an object named '#test' in the database.

ifs 中的 drop table 组合(在创建表 DDL 之前或之后)似乎无法满足 sql 检查器.

No combination of drop table inside the ifs (before or after the create table DDL) seems to satisfy the sql checker.

任何想法我怎么能做到这一点?例如，我可以告诉 SQL 不执行语法检查而直接接受 sproc 吗?

Any ideas how I can do this? Can I, for example, tell SQL to not perform the syntax check and just accept the sproc as is?

推荐答案

这是一个限制.动态 SQL 也不起作用，因为 #tmp 将在新会话中创建并立即丢失.对于如图所示的 EXACT 代码段，其作用相同

It's a limitation. Dynamic SQL won't work either since #tmp will be created in a new session and immediately lost. For the EXACT snippet as shown, this does the same

CREATE TABLE #test
(
    a BIGINT NOT NULL,
    b BIGINT NOT NULL
)

IF not (1=1)
    ALTER TABLE #test ADD c BIGINT NOT NULL   

同一批次中不能有两个 CREATE .. #name，但这也适用于一般形式

There cannot be two CREATE .. #name within the same batch, but this will also work in general form

IF (1=1)
BEGIN
    CREATE TABLE #test
    (
        a BIGINT NOT NULL,
        b BIGINT NOT NULL
    );
END 
GO

IF NOT (1=1)
BEGIN
    CREATE TABLE #test
    (
        a BIGINT NOT NULL,
        b BIGINT NOT NULL,
        c BIGINT NOT NULL   
    )
END

这篇关于为什么 SQL Server 认为临时表已经存在，但实际上并不存在?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！