Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Unable to create Proecdure with temp table please help me

Answered Unable to create Proecdure with temp table please help me

  • Tuesday, February 26, 2013 7:17 PM
     
     
    Sign In to Vote
    0

    Hello All,

    Please help me here, i am fighting with this since a day, could somebody please hlep me out here,

    i try to create below proecdure, it throws the below error, could you please assistme

    Msg 2714, Level 16, State 1, Procedure stat_med_operationspoc, Line 9
    There is already an object named '#tmp_tb_Platter' in the database.

    USE [StamfordMedGrp_DEV]
    GO
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO

    CREATE procedure [dbo].[stat_med_operationspoc]
    @strt_Date datetime,
    @end_Date datetime

    AS
    BEGIN

    Declare ......

    if object_id('tempdb..#tmp_tb_Platter') is not null
                   drop table tempdb..#tmp_tb_Platter

    CREATE TABLE #tmp_tb_Platter
    (
       
        [CreatedDateTime] datetime NOT NULL,
        [ReasonMedical] nvarchar(50) NULL,
        [Operation] varchar(50) NOT NULL,
        [AgentId] bigint NULL,
        [ChartRef] nvarchar(50) NULL
    )

    Insert into #tmp_tb_Platter

    select ..... from table1

    .....

    END

     

All Replies

  • Tuesday, February 26, 2013 7:28 PM
    Moderator
     
     
    Sign In to Vote
    0

    Can you post the rest of the code?

    A local temporary table is out of scope when the procedure ends. You should not have any issue re-running the sp or running it concurrently.

    AMB

    Some guidelines for posting questions...

     
  • Tuesday, February 26, 2013 7:34 PM
    Moderator
     
     Answered
    Sign In to Vote
    0

    Just to expand on Hunchback's response.

    There is no reason for this code:

    if object_id('tempdb..#tmp_tb_Platter') is not null
                   drop table tempdb..#tmp_tb_Platter

    The temp table will be automatically dropped at the end of the proc.

    The only reason you would get that error is if you try to recreate the same table in the same stored proc again.  This is not allowed.

    • Marked As Answer by asitti7 Tuesday, February 26, 2013 7:47 PM
    •  
     
  • Tuesday, February 26, 2013 7:34 PM
     
     Answered
    Sign In to Vote
    0
    You normally don't need it. Just create your table, work with it and then drop it. When you use error handling (try-catch) then you can ensure that you reach the drop at the end correctly. But on the other hand: consider using a table variable, when it doesn't hurt performance.
    • Marked As Answer by asitti7 Tuesday, February 26, 2013 7:47 PM
    •  
     
  • Tuesday, February 26, 2013 7:43 PM
     
     Proposed
    Sign In to Vote
    1

    Hi, 

    You just need to remove below line of code from your procedure. It is of no use, if you want then use simple drop table #tmp_tb_Platter script

    "  if object_id('tempdb..#tmp_tb_Platter') is not null 
                   drop table tempdb..#tmp_tb_Platter "

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

    USE [StamfordMedGrp_DEV] 
    GO
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO

    CREATE procedure [dbo].[stat_med_operationspoc]
    @strt_Date datetime,
    @end_Date datetime

    AS
    BEGIN

    Declare ......

    if object_id('tempdb..#tmp_tb_Platter') is not null 
                   drop table tempdb..#tmp_tb_Platter

    CREATE TABLE #tmp_tb_Platter
    (
        
        [CreatedDateTime] datetime NOT NULL,
        [ReasonMedical] nvarchar(50) NULL,
        [Operation] varchar(50) NOT NULL,
        [AgentId] bigint NULL,
        [ChartRef] nvarchar(50) NULL
    )

    Insert into #tmp_tb_Platter

    select ..... from table1

    .....

    END

    Please Mark as Answer if my post solved your problem or Vote As Helpful if this helps. Blogs: www.sqlserver2005forum.blogspot.com

    • Proposed As Answer by Anurag_India Wednesday, February 27, 2013 8:40 PM
    •  
     
  • Tuesday, February 26, 2013 7:47 PM
     
      Has Code
    Sign In to Vote
    0

    Hi Tom

    The only reason you would get that error is if you try to recreate the 
same table in the same stored proc again.  This is not allowed.

    Great thanks, this procedure is 3427 lines long i pasted twice, thats the deal excellent, i did removed one code then it is working fine

    Thanks to Hunch Back, Tom, Stefan  i Bow My head, i great ful to you all,

    Thanks & Best Regards

    Asiti