Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2008
    Posts
    24

    Unanswered: Re-use of temp table name within a single stored proc?

    Hello,

    Is it possible to re-use a temp table name within a single stored proc?

    E.g.

    SELECT <some stuff> INTO #temp FROM ...

    -- use <some stuff>

    SELECT <some other stuff with different columns> INTO #temp FROM ...

    DROP TABLE #temp doesn't work - it indicates that #temp already exists in the database.

    Thanks, chasse

  2. #2
    Join Date
    Jan 2010
    Posts
    25
    you cannot have 2 temp tables with same name.

  3. #3
    Join Date
    Aug 2008
    Posts
    24
    Thanks, alirulez999!

  4. #4
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    You can always drop the table before creating it again

  5. #5
    Join Date
    Aug 2008
    Posts
    24
    Hi pdreyer -

    I had hoped so, but as stated in the original post:

    DROP TABLE #temp doesn't work - it indicates that #temp already exists in the database.

    I guess that even though the DROP statment is BEFORE the logic that attempts to 're-create' the table, it isn't actually executed until the session is terminated (i.e. the stored proc has ended).

    That's all I can figure ...

    This, unfortunately, leaves me having to create 18 temp tables ...

    Any other suggestions appreciated!
    Thanks!

  6. #6
    Join Date
    Jun 2009
    Location
    India
    Posts
    50
    Yes, it is possible to use temporary table within a SP.

    Above comments from "pdreyer" and "alirulez999" are correct.


    1> select @@version
    2> go

    ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    Adaptive Server Enterprise/15.0.2/EBF 15651 ESD#4/P/Sun_svr4/OS 5.8/ase1502/2528/64-bit/FBO/Sat Apr 5 09:36:08 2008
    (1 row affected)

    #Creating temporary table (#temp)
    1> select * into #temp from signal where 1=2
    2> go
    (0 rows affected)

    # temporary table (#temp) exists
    1> select object_id("#temp")
    2> go

    -----------
    69157599

    (1 row affected)

    # When we try to insert data into #temp table, ASE throws error because temporary (#temp) table already exists. You can not have two temp tables with same name
    1> select * into #temp from signal where 1=2
    2> go
    Msg 12822, Level 16, State 1:
    Server 'TEST', Line 1:
    Cannot create temporary table '#temp'. Prefix name '#temp' is already in use by another temporary table '#temp'.

    #Creating temporary table (#temp2)
    1> select * into #temp2 from signal where 1=2
    2> go
    (0 rows affected)

    # temporary tables (#temp & #temp2) exists
    1> select object_id("#temp"), object_id("#temp2")
    2> go

    ----------- -----------
    69157599 821160278

    (1 row affected)

    # droping temporary tables (#temp & #temp2)
    1> drop table #temp
    2> go
    1> drop table #temp2
    2> go

    # Confirmed that temporary tables (#temp & #temp2) are dropped
    1> select object_id("#temp"), object_id("#temp2")
    2> go

    ----------- -----------
    NULL NULL

    (1 row affected)
    Last edited by Neevarp; 02-19-10 at 10:44.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •