在 IF ELSE 语句中删除临时表 [英] Drop temp table within IF ELSE statement

问题描述

我在这里遇到了僵局，问题是我必须更改一个使用 3 个不同临时表的程序.为了对话方便，让我们将它们命名为 #temptable1、#temptable2、#temptable3.我不能在这里复制/粘贴整个过程，但总体思路是这样的，原始过程(procedure1)在过程的最开始创建#temptable1

I am facing a deadlock here, the issue is that I have to alter a procedure which makes use of 3 different temp tables. Lets for the sake of the conversation name them #temptable1, #temptable2, #temptable3. I cannot copy/paste the whole procedure here but the general idea is this, The original procedure (procedure1) creates the #temptable1 at the very beginning of the process

create table #temptable1

然后使用 select/into 语句创建剩余的两个

then creates the remaining two by using a select/into statement

select  T.Col
    ,   T.Col2
    ,   T.Col3
into   #temptable2
from   table1 T
where  T.BB>0

select  T.Col
    ,   T.Col2
    ,   T.Col3
into   #temptable3
from   table2 T
where  T.BB>0

drop table #temptable1
drop table #temptable2
drop table #temptable3

到目前为止，它工作正常，但我想要做的是通过添加 if/else 语句来更改过程.因此看起来像那样，

Until this point it works fine, but what I want to do is to alter the procedure by adding an if/else statement. Thus to look like that,

declare @BBB nvarchar(32)

create table #temptable1


if @BBB='dd'

begin 

select  T.Col
        ,   T.Col2
        ,   T.Col3
    into   #temptable2
    from   table1 T
    where  T.BB>0 and T.G='FDL'

    select  T.Col
        ,   T.Col2
        ,   T.Col3
    into   #temptable3
    from   table2 T
    where  T.BB>0 and T.G='FDL'

    drop table #temptable1
    drop table #temptable2
    drop table #temptable3
end 

if @BBB='kk'

begin 

select  T.Col
        ,   T.Col2
        ,   T.Col3
    into   #temptable2
    from   table1 T
    where  T.BB>0 and T.G='FD'

    select  T.Col
        ,   T.Col2
        ,   T.Col3
    into   #temptable3
    from   table2 T
    where  T.BB>0 and T.G='FD'

    drop table #temptable1
    drop table #temptable2
    drop table #temptable3
end

else 

begin 

select  T.Col
        ,   T.Col2
        ,   T.Col3
    into   #temptable2
    from   table1 T
    where  T.BB>0

    select  T.Col
        ,   T.Col2
        ,   T.Col3
    into   #temptable3
    from   table2 T
    where  T.BB>0

    drop table #temptable1
    drop table #temptable2
    drop table #temptable3
end

当我尝试创建新程序时，我收到此消息，

When I try to create the new procedure I get this message,

Msg 2714, Level 16, State 1, Procedure pPortfoliostest3, Line 412
There is already an object named '#temptable1' in the database.
Msg 2714, Level 16, State 1, Procedure pPortfoliostest3, Line 550
There is already an object named '#temptable2' in the database.
Msg 2714, Level 16, State 1, Procedure pPortfoliostest3, Line 711
There is already an object named '#temptable3' in the database.

这些行表示临时表在第二个 if 语句中的位置(如果 @BBB='kk').我尝试了不同的组合，但无济于事.有小费吗?感谢您抽出宝贵时间.

The lines are indicating where the temp tables are within the second if statement (if @BBB='kk'). I have tried different combinations but to no avail. Any tips? Thank you for your time.

推荐答案

T-SQL 解析器非常原始.特别是，控制流不会影响对象名称何时进入范围和保持在范围内.

The T-SQL parser is remarkably primitive. In particular, control flow doesn't affect when object names come into scope and remain in scope.

因此，您在 if 分支中使用的名称仍在范围内，并且在解析 else 分支时会导致冲突.

So the names you're using in your if branch are still in scope and cause a conflict when the else branch is parsed.

如果可能，将表定义移到存储过程的顶部，在任何控制流之前，并切换到 INSERT ... SELECT ... 而不是 SELECT ...进入...;或者，如果 if 和 else 分支之间的表定义不匹配，则需要为临时表使用不同的名称.

If possible, move the table definitions up to the top of the stored procedure, before any control flow, and switch to INSERT ... SELECT ... rather than SELECT ... INTO ...; or if the table definitions don't match between the if and else branches, you'll need to use different names for the temp tables.

作为解析器是多么原始的另一个例子，请考虑以下内容:

As another example of how primitive the parser is, consider the following:

if 1=0
begin
    declare @a int
end
select @a

这会生成一个包含 null 的结果集，而不是(如您所料)一个错误，指出 @a 未声明.

This produces a result set containing null rather than (as you might have expected) an error saying that @a isn't declared.

这篇关于在 IF ELSE 语句中删除临时表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！