Post reply

someone knows where is the information of the global tmep tables

  • January 27, 2005 at 2:18 pm

    #163710

    that's all

    I want to drop a global temp table only if it's already created

  • January 27, 2005 at 2:24 pm

    #539438

    use tempdb

    if exists ( select * from information_schema.tables where table_name = '##YourTableName')

      Drop Table ##YourTableName

  • January 27, 2005 at 3:01 pm

    #539448

    Alternatively:

    IF OBJECT_ID('tempdb..##stuff') is not null

        DROP TABLE ##stuff

    Avoids direct access to system tables. Supplemental checks for "is ##stuff actually a table" might be advisable. Also, while I haven't tested this, (a) you should only be able to drop it via the connection [spid] that made it, and (b) if any other connection is using it (within a transaction?) the drop should fail.

       Philip

     

  • January 28, 2005 at 6:07 am

    #539510

    thx both... i was missing the "use tempdb" statement when I try to search the table name in information_schema.tables

    thx both of you

  • January 28, 2005 at 8:01 am

    #539540

    Just a minor clarification on 'global' temp tables. If it is truly global (i.e ## as stated) and not in a transaction, anyone may drop it. Just take query analyzer and in window 1 execute: create table ##temp (c1 int). The open another query analyzer pane or instance (there by creating another spid) and execute drop table ##temp.

    RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

  • January 28, 2005 at 12:45 pm

    #539602

    that's not the problem.. the problem was that I have multiple procedures, and I need to use the output table of another procedure, and in order to do that i create a global temp table. But I need to change te values of the table executing the procedure with other parameters. And I want to leave the drop table in the same procedure, that way I don't have to worry about that table.

    So I finally use the answer in the second post, because USE it's not allow inside a procedure

  • January 28, 2005 at 12:56 pm

    #539604

    Do you really need a global temp table then ?

    If the purpose is just share data in a temp table between stored procedures, you only need a local temp table, declared in the outermost procedure.

     

  • January 28, 2005 at 1:17 pm

    #539606

    but the procedures are not nested, and a temp table It's destroyed when the create procedure finish

  • January 28, 2005 at 1:58 pm

    #539612

    Ahh, I assumed nesting. You could always choose to nest, by creating 1 overall procedure that creates the local temp table, then calls the others in the same way that you are currently calling them.

  • January 28, 2005 at 4:40 pm

    #539635

    You might also think about using a UDF for this. That way you don't have to create a global table and anytime the parameters are the same you should get the same results. Just a thought!

    Of course this means you have to be running SQL2K.




    Gary Johnson
    Microsoft Natural Language Group
    DBA, Sr. DB Engineer

    This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply