locked
DROP TABLE does not seem to work. RRS feed

  • Question

  • Hi!

    I am building a long Stored Procedure in Management Studio.

    It consists essentially of a chain of queries, each independent of the others. Each query generates a specific table.

    I have finished writing the 1st query in the chain. It uses a number of Local temporary tables (e.g #RDR, #RDN, ...)

    Near the end of the query, I DROP all local temporary tables:

             DROP TABLE #RDR, #RDN, ... etc

    ---------------------

    Then I add my 2nd Query. It starts like this:

             CREATE TABLE #RDR ( .... fields .....)

    ---------------------

    When I run the SP, IT GIVES THIS ERROR MSG:

             There is already an object named #RDR in the database

    It seems that the DROP TABLE command did not work.

    I tried DROP TABLE temp.dbo.#RDR,

    Also tried IF OBJECT_ID('temp.dbo.#RDR') is not null DROP TABLE temp.dbo.#RDR . Both do not work.

    ------------------------

    My Questions

    (a) Query1 has dropped the #Table. Why does Query2 say #Table still exists?

    (b) How should I put my numerous queries so that each one executes after the previous one.

         Just put one after another? Or Put GO after each query?

    Thanks

    Leon

         

    Thursday, December 20, 2012 3:50 PM

Answers

  • can you try this way?  and remember SQL would give you trouble if you have a two CREATE TABLE statement with same table name...

    IF OBJECT_ID('tempdb..#RDR') IS NOT NULL 	--Remove dbo here 
    	DROP TABLE #RDR   -- Remoeve "tempdb.dbo"
    	
    -- DROP TABLE tempdb.dbo.#RDR
    CREATE TABLE #RDR(
    RDR_U_SHipDept nVarChar(30),
    RDR_DocDate date,
    RDR_Project nVarChar(8),
    RDR_PrjName nVarChar(30),
    RDR_ItemCode nVarChar(20),
    RDR_unitMsr nVarChar(20),
    RDR_Quantity Numeric(19,6),
    RDR_Currency nVarChar (3),
    RDR_Price Numeric(19,6),
    RDR_Rate Numeric(19,6),
    RDR_TotalFrgn Numeric(19,6),
    RDR_LineTotal Numeric(19,6))

    Regards
    satheesh


    • Edited by Satheesh Variath Thursday, December 20, 2012 5:32 PM
    • Marked as answer by Naomi N Sunday, December 23, 2012 7:06 AM
    Thursday, December 20, 2012 5:29 PM
  • You cannot have to CREATE TABLE statements for the same table in a stored procedure. As for why, I say that this is a holdover from SQL 6.5 when there was no deferred name resolution, and you would get a compile-time error if you misspelled a column in a temp table, or misspelled a permanent table.

    That is, at compile time, SQL Server would use the table definition to validate the procedure. It would be problematic if there was multiple definitions for the same name.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by Naomi N Thursday, December 20, 2012 11:41 PM
    • Marked as answer by Naomi N Sunday, December 23, 2012 7:05 AM
    Thursday, December 20, 2012 11:22 PM
  • You will have to give unique names to the temp tables.  Maybe you can do something like #RDR1.

    Kathi Kellenberger

    Convert DTS to SSIS | Document SSIS | 30+ SSIS Tasks | Real-time SSIS Monitoring | Quick Starts | BI Blitz

    • Marked as answer by Leon Lai Friday, December 21, 2012 7:13 AM
    Thursday, December 20, 2012 4:20 PM
  • When you say you "run" the SP, do you mean try to create it?

    If you reference a table, then try to create it further down, the parser throws an error.  It doesn't matter that you've dropped it inbetween.  Change the name of the temp table then retry.

    • Marked as answer by Leon Lai Friday, December 21, 2012 7:15 AM
    Thursday, December 20, 2012 4:42 PM

All replies

  • You will have to give unique names to the temp tables.  Maybe you can do something like #RDR1.

    Kathi Kellenberger

    Convert DTS to SSIS | Document SSIS | 30+ SSIS Tasks | Real-time SSIS Monitoring | Quick Starts | BI Blitz

    • Marked as answer by Leon Lai Friday, December 21, 2012 7:13 AM
    Thursday, December 20, 2012 4:20 PM
  • Hi Leon,

    Please can you paste your code as it seems that you are trying to create the table that already exists on the session. I suggest you to format your SQL Store procedure as follow:

    -- Drop stored procedure if it already exists
    IF EXISTS (
      SELECT * 
        FROM INFORMATION_SCHEMA.ROUTINES 
       WHERE SPECIFIC_SCHEMA = N'<Schema_Name, sysname, Schema_Name>'
         AND SPECIFIC_NAME = N'<Procedure_Name, sysname, Procedure_Name>' 
    )
       DROP PROCEDURE <Schema_Name, sysname, Schema_Name>.<Procedure_Name, sysname, Procedure_Name>
    GO
    CREATE PROCEDURE <Schema_Name, sysname, Schema_Name>.<Procedure_Name, sysname, Procedure_Name>
    	<@param1, sysname, @p1> <datatype_for_param1, , int> = <default_value_for_param1, , 0>, 
    	<@param2, sysname, @p2> <datatype_for_param2, , int> = <default_value_for_param2, , 0>
    AS
    	--  CREATING Temp Tables
    	IF OBJECT_ID('Tempdb.dbo.#RDR ') IS NOT NULL  
    	 DROP TABLE #RDR   
    	CREATE TABLE #RDR ([Column1], [Column2]...[ColumnN])
    	IF OBJECT_ID('Tempdb.dbo.#RDN') IS NOT NULL  
    	 DROP TABLE RDN 
    	CREATE TABLE RDN ([Column1], [Column2]...[ColumnN])
    	-- YOUR PROCEDURE LOGIC GOES HERE --
    	IF OBJECT_ID('Tempdb.dbo.#RDR ') IS NOT NULL  
    	 DROP TABLE #RDR   
    	IF OBJECT_ID('Tempdb.dbo.#RDN') IS NOT NULL  
    	 DROP TABLE RDN 
    	SELECT @p1, @p2
    GO


    Regards,

    Basit A. Farooq (MSC Computing, MCITP SQL Server 2005 & 2008, MCDBA SQL Server 2000)

    http://basitaalishan.com

    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

    • Proposed as answer by Basit Farooq Thursday, December 20, 2012 4:26 PM
    • Unproposed as answer by Naomi N Thursday, December 20, 2012 11:41 PM
    Thursday, December 20, 2012 4:23 PM
  • Hi Basit,

    Thanks for your reply.

    My SP is 10 pages long! Too long and complex to paste here.

    But I am pasting the end of Query 1 and beginning of Query 2.

    Hope this helps.

    Best Regards

    Leon

    ---------------------------------------------

    DROP TABLE #RDR, #DLN, #RDN, #INV, #RIN, #POR, #PDN, #RPD, #PCH, #RPC, #IGNtfr, #IGEtfr, #IGNadj, #IGEadj
    EXEC ('SELECT * INTO ' + @TblSub01 + ' FROM #Main00')

    SELECT RDR_DocDate, RDR_Project, RDR_PrjName From #Main00

    ---end of Query1
    ------------------------
    --Start of Query2

    IF OBJECT_ID('tempdb.dbo.#RDR') IS NOT NULL DROP TABLE tempdb.dbo.#RDR

    -- DROP TABLE tempdb.dbo.#RDR

    CREATE TABLE #RDR(
    RDR_U_SHipDept nVarChar(30),
    RDR_DocDate date,
    RDR_Project nVarChar(8),

    RDR_PrjName nVarChar(30),
    RDR_ItemCode nVarChar(20),
    RDR_unitMsr nVarChar(20),
    RDR_Quantity Numeric(19,6),

    RDR_Currency nVarChar (3),
    RDR_Price Numeric(19,6),
    RDR_Rate Numeric(19,6),

    RDR_TotalFrgn Numeric(19,6),
    RDR_LineTotal Numeric(19,6))


    • Edited by Leon Lai Thursday, December 20, 2012 4:49 PM
    Thursday, December 20, 2012 4:33 PM
  • When you say you "run" the SP, do you mean try to create it?

    If you reference a table, then try to create it further down, the parser throws an error.  It doesn't matter that you've dropped it inbetween.  Change the name of the temp table then retry.

    • Marked as answer by Leon Lai Friday, December 21, 2012 7:15 AM
    Thursday, December 20, 2012 4:42 PM
  • Hi Kathi,

    Thanks for your reply.

    You are right: If we give unique names, it works.

    But I am really puzzled. If you drop #RDR, it is gone! And it should be possible to create a new #RDR!

    Why does it not work as expected?

    And also, I would like to retain the name #RDR for uniformity in the dozen of queries that follow the 1st one.

    Best Regards

    Leon

    Thursday, December 20, 2012 4:46 PM

  • ------------------------
    --Start of Query2

    IF OBJECT_ID('tempdb.dbo.#RDR') IS NOT NULL DROP TABLE tempdb.dbo.#RDR

    Try replacing the above statement with the below -

    IF OBJECT_ID('tempdb..#RDR') IS NOT NULL 
    BEGIN 
    	DROP TABLE #RDR
    END 


    Narsimha

    Thursday, December 20, 2012 4:52 PM
  • Hi mzz3lh!

    I mean I execute it

    You mean even if a #table is dropped it "still remains sort of alive"?

    However if I highlight the DROP TABLE #RDR manually and EXECUTE this, I can create another #RDR without problem.

    What's the difference between doing it manually and leaving the SP to do it?

    Thanks

    Leon




    • Edited by Leon Lai Thursday, December 20, 2012 5:07 PM
    Thursday, December 20, 2012 4:52 PM
  • Hi Leon,

    As suggested, see the stored procedure template I pasted in my previous reply. If you follow that logic, it should always work.


    Regards,

    Basit A. Farooq (MSC Computing, MCITP SQL Server 2005 & 2008, MCDBA SQL Server 2000)

    http://basitaalishan.com

    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

    Thursday, December 20, 2012 4:54 PM
  • can you try this way?  and remember SQL would give you trouble if you have a two CREATE TABLE statement with same table name...

    IF OBJECT_ID('tempdb..#RDR') IS NOT NULL 	--Remove dbo here 
    	DROP TABLE #RDR   -- Remoeve "tempdb.dbo"
    	
    -- DROP TABLE tempdb.dbo.#RDR
    CREATE TABLE #RDR(
    RDR_U_SHipDept nVarChar(30),
    RDR_DocDate date,
    RDR_Project nVarChar(8),
    RDR_PrjName nVarChar(30),
    RDR_ItemCode nVarChar(20),
    RDR_unitMsr nVarChar(20),
    RDR_Quantity Numeric(19,6),
    RDR_Currency nVarChar (3),
    RDR_Price Numeric(19,6),
    RDR_Rate Numeric(19,6),
    RDR_TotalFrgn Numeric(19,6),
    RDR_LineTotal Numeric(19,6))

    Regards
    satheesh


    • Edited by Satheesh Variath Thursday, December 20, 2012 5:32 PM
    • Marked as answer by Naomi N Sunday, December 23, 2012 7:06 AM
    Thursday, December 20, 2012 5:29 PM
  • You cannot have to CREATE TABLE statements for the same table in a stored procedure. As for why, I say that this is a holdover from SQL 6.5 when there was no deferred name resolution, and you would get a compile-time error if you misspelled a column in a temp table, or misspelled a permanent table.

    That is, at compile time, SQL Server would use the table definition to validate the procedure. It would be problematic if there was multiple definitions for the same name.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by Naomi N Thursday, December 20, 2012 11:41 PM
    • Marked as answer by Naomi N Sunday, December 23, 2012 7:05 AM
    Thursday, December 20, 2012 11:22 PM
  • Hi Kathi,

    I now understand that it is not possible to CREATE a temp table twice in a SP.

    The simplest workaround is to use a unique name as you suggested.

    Thanks and Best Regards,

    Leon

    Friday, December 21, 2012 7:12 AM
  • Hi mzz3lh,

    I now understand that it is not possible to CREATE a temp table twice in a SP.

    The simplest workaround is to change the name as you suggest.

    Thanks and Best Regards,

    Leon

    Friday, December 21, 2012 7:15 AM
  • Hi Erland,

    Thanks a lot for your response.

    It is a very clear explanation why I cannot create a temp table twice in a SP.

    Best Regards

    Leon

    • Marked as answer by Leon Lai Friday, December 21, 2012 7:16 AM
    • Unmarked as answer by Naomi N Sunday, December 23, 2012 7:05 AM
    Friday, December 21, 2012 7:16 AM
  • Hi Naarasimha,

    Thanks for your response.

    Unfortunately the suggestion does not work.

    I now understand that the problem is not in the dropping of the table.

    The problem is that we cannot create a temp table twice in a SP.

    Best Regards,

    Leon

    Friday, December 21, 2012 7:21 AM
  • Hi,

    Instead you could have a CREATE TABLE once and reuse it all the time. use truncate in case of DROP.

    Regards
    satheesh

    Friday, December 21, 2012 7:23 AM
  • HI Satheesh,

    Thanks for your response.

    I now understand that it is not possible to create a table twice in a SP even if it has been dropped.

    Best Regards

    Leon

    • Marked as answer by Leon Lai Friday, December 21, 2012 7:24 AM
    • Unmarked as answer by Naomi N Sunday, December 23, 2012 7:06 AM
    Friday, December 21, 2012 7:24 AM
  • Hi Basit,

    Thanks for your response.

    I now understand that it is not possible to create a temp table twice in a SP, even if it has been dropped.

    It is possible that your suggestion could work, and I am studying it.

    But other readers have suggested a simpler solution: Use another table name.

    Best Regards,

    Leon

    Friday, December 21, 2012 7:29 AM