Friday, February 24, 2012

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.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.

No comments:

Post a Comment