Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2003
    Posts
    176

    Unanswered: common temp tables in procedures

    I have 3 jobs each consists of set of stored procedures.The stored procedures have lots of temp tables. And all the jobs run at the same time.

    job1:

    EXEc sp1
    EXEC sp2
    EXEC sp3

    Job2

    EXEC abc1
    EXEC abc2
    EXEC abc3
    EXEC abc4
    EXEC abc5

    Job3

    EXEC xyz1
    EXEC xyz2
    EXEC xyz3
    EXEC xyz4


    But the issue is that the stored procedures in the job1 has temp tables with the same name as stored procedures in the job 2 have.

    Eg:

    procedure abc1 and procedure sp2 have the temp table #temp1.
    procedure abc4 and procedure xyz2 have the temp table #temp3.

    Like this i have some more common temp tables. So my question is that can I use the temp tables like that.If so does it degrade the perforamnce of the sps.

  2. #2
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    You can and there is no performance penalty. You might get a performance increase by using table variables though, depending on how small the data is. Temp tables are local to a procedure, so having them named the same in two procs that call each other isn't a problem.
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

Posting Permissions

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