Início / dba / Perguntas / 49628
Accepted
Kermit
Asked: 2013-09-11 14:50:12 +0800 CST

se o bloco falhar ao criar tabela temporária em um procedimento

  • 772

Estou tentando fazer isso em um procedimento:

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

Eu recebo o erro:

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

Por que isso está acontecendo e há uma solução alternativa?

Atualizar

Tentei adicionar uma DROPdeclaração conforme sugerido aqui , mas ainda não funcionou:

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 respostas

  1. Best Answer

    Pela documentação :

    Se mais de uma tabela temporária for criada dentro de um único procedimento armazenado ou lote, elas deverão ter nomes diferentes.

    Acabei criando a tabela antes do IFbloco assim:

    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. Esta NÃO é uma resposta para a pergunta, apenas uma demonstração para @SebastienMeine como ##tabelas temporárias globais podem eliminar a simultaneidade.

    Em uma janela, faça o seguinte:

    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;

    Resultado:

    a
----
1

    Em seguida, abra outra janela e faça o seguinte:

    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;

    Resultados:

    Algo ruím aconteceu.
    Msg 2714, nível 16, estado 6, procedimento floob1
    Já existe um objeto denominado '##floob' no banco de dados.

    a
----
1

    Portanto, a tabela ## ainda existe, embora o procedimento em que foi criado tenha terminado há muito tempo.

    Depois que a primeira janela terminar, tente executar esta parte na primeira janela novamente:

    EXEC dbo.floob1 @p = 1;

    Resultado:

    Msg 2714, nível 16, estado 6, procedimento floob1
    Já existe um objeto denominado '##floob' no banco de dados.

    Portanto, a tabela ## ainda existe, embora o mesmo usuário esteja simplesmente tentando criá-la novamente.

    Isso ilustra que:

    1. apenas um usuário pode efetivamente chamar este procedimento por vez
    2. o conselho padrão de não se preocupar em descartar tabelas #temp (sobre as quais estou em dúvida) não se aplica da mesma maneira a tabelas ##temp.
    • 3

  3. Aqui está uma solução que eu uso se a tabela temporária não puder ser criada antecipadamente e não quiser colocar a lógica central no SQL dinâmico.

    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

relate perguntas