主页 / dba / 问题 / 49628
Accepted
Kermit
Asked: 2013-09-11 14:50:12 +0800 CST

如果块无法在过程中创建临时表

  • 772

我试图在一个过程中做到这一点：

DECLARE @a bit = 1;
BEGIN
    SELECT * INTO #aTemp FROM OPENROWSET( ... );

    IF @a = 0
    BEGIN
        SELECT ... INTO #bTemp FROM #aTemp;
    END
    ELSE
    BEGIN
        SELECT ... INTO #bTemp FROM #aTemp;
    END
END

我得到错误：

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

为什么会发生这种情况，是否有解决方法？

更新

我试图按照这里DROP的建议添加一个语句，但它仍然不起作用：

DECLARE @a bit = 1;
BEGIN
    SELECT * INTO #aTemp FROM OPENROWSET( ... );

    IF @a = 0
    BEGIN
        IF OBJECT_ID('[tempdb]..#bTemp') IS NOT NULL
        BEGIN
            DROP TABLE #bTemp;
        END

        SELECT ... INTO #bTemp FROM #aTemp;
    END
    ELSE
    BEGIN
        IF OBJECT_ID('[tempdb]..#bTemp') IS NOT NULL
        BEGIN
            DROP TABLE #bTemp;
        END

        SELECT ... INTO #bTemp FROM #aTemp;
    END
END
sql-server t-sql

3 个回答

  1. Best Answer

    根据文档：

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

    我最终在块之前创建了表格，IF如下所示：

    DECLARE @a bit = 1;
BEGIN
    IF OBJECT_ID('[tempdb]..#bTemp') IS NOT NULL
    BEGIN
        DROP TABLE #bTemp;
    END

    CREATE TABLE #bTemp (
        [c] int);

    IF @a = 0
    BEGIN
        INSERT INTO #bTemp
        SELECT 1 AS [c];
    END
    ELSE
    BEGIN
        INSERT INTO #bTemp
        SELECT 1 AS [c];
    END

    DROP TABLE #bTemp;
END
    • 4

  2. 这不是问题的答案，只是向@SebastienMeine 演示##global 临时表如何杀死并发。

    在一个窗口中，执行以下操作：

    CREATE PROCEDURE dbo.floob1
  @p INT
AS
BEGIN
  SET NOCOUNT ON;

  SELECT a = @p INTO ##floob;
END
GO

CREATE PROCEDURE dbo.floob2
AS
BEGIN
  SET NOCOUNT ON;

  SELECT a FROM ##floob;
END
GO

EXEC dbo.floob1 @p = 1;
WAITFOR DELAY '00:01:00';
EXEC dbo.floob2;

    结果：

    a
----
1

    然后打开另一个窗口，执行以下操作：

    BEGIN TRY
  EXEC dbo.floob1 @p = 2;
END TRY
BEGIN CATCH
  PRINT 'Something bad happened.';
END CATCH
GO
EXEC dbo.floob1 @p = 2;
GO
EXEC dbo.floob2;

    结果：

    发生了不好的事情。
    消息 2714，级别 16，状态 6，过程 floob1
    数据库中已经有一个名为“##floob”的对象。

    a
----
1

    所以##table 仍然存在，即使它创建的过程早已完成。

    然后在第一个窗口完成后，再次尝试在第一个窗口中运行此部分：

    EXEC dbo.floob1 @p = 1;

    结果：

    消息 2714，级别 16，状态 6，过程 floob1
    数据库中已经有一个名为“##floob”的对象。

    因此，即使同一用户只是尝试再次创建，##table 仍然存在。

    这说明：

    1. 一次只有一个用户可以有效地调用此过程
    2. 不要打扰删除#temp 表（我对此持怀疑态度）的标准建议不适用于##temp 表。
    • 3

  3. 如果无法预先创建临时表并且不想将核心逻辑放在动态 SQL 中，这是我使用的解决方案。

    IF 1 = 1 -- Replace with actual condition
BEGIN
    SELECT * INTO #tmp1 FROM dbo.Table1
END
ELSE
BEGIN
    SELECT * INTO #tmp2 FROM dbo.Table2
END

-- Inserting data into global temp table so sql server can't complain on not recognizing in a context
DECLARE @Command VARCHAR(MAX)
IF OBJECT_ID('tempdb..#tmp1') IS NOT NULL
BEGIN
    SET @Command = 'SELECT * INTO ##tmp FROM #tmp1'
END
ELSE
BEGIN
    SET @Command = 'SELECT * INTO ##tmp FROM #tmp2'
END

EXECUTE(@Command)
SELECT * INTO #tmpFinal FROM ##tmp -- Again passing data back to local temp table from global temp table to avoid seeing red mark

IF OBJECT_ID('tempdb..##tmp') IS NOT NULL DROP TABLE ##tmp
IF OBJECT_ID('tempdb..#tmp1') IS NOT NULL DROP TABLE #tmp1
IF OBJECT_ID('tempdb..#tmp2') IS NOT NULL DROP TABLE #tmp2

SELECT * FROM #tmpFinal

IF OBJECT_ID('tempdb..#tmpFinal') IS NOT NULL DROP TABLE #tmpFinal
    • 0

相关问题