is sharing temp tables across procedures considered bad form?

Question

I have 2 procedures. One that builds a temp table and another (or several others) that use a temp table that's created in the first proc. Is this considered bad form? I'm walking into an existing codebase here and it uses this pattern a lot. It certainly bothers me but I can't say that it's explicitly a bad idea. I just find it to be an annoying pattern -- something smells rotten but I can't say what. I'd prefer to build the table locally and then fill it with an exec. But that requires procs that only return one table, which is unusual in the existing codebase.

Do the gurus of SQL shy away from this sort of temp table sharing? If so, why? I'm trying to form a considered opinion about this and would like some input.

Would a view be a viable alternative?

What about performance? Would it be better to build a @table locally or build a #table in the "fill" proc?

There's a good discussion of all methods here: http://www.sommarskog.se/share_data.html

Answer 1

I've encountered this same exact problem. What I can say from personal experience:

If there's a way to avoid using a #temp table across multiple procedures, use it. #temp tables are easy to lose track of and can easily grow tempdb if you're not careful.

In some cases, this method is un-avoidable (my classic example is certain reporting functionality that builds data differently based on report configration). If managed carefully, I believe that in these situations it is acceptable.

Answer 2

Sharing the temp tables is not a bad idea. But we need to ensure that the table level data should not be manipulated by the two procs at the same time, leading to Dirty Read/Write scenario. This will also help to have a single table [centralized ] and the procs working on it, to have sync-up data. Regarding performance, it is true that, having multiple procs sharing the same temp data, will decrease performance. But at the same time, having individual tables [ per Proc ] will lead to increased memory consumption,

Answer 3

I have used this approach in a few cases. But I always declare the temp table as a fully qualified table and not by using #tablename.

CREATE TABLE [tempdb].[dbo].[tablename]

with this approach, it is easy to keep track of the temp table.

Raj

Answer 4

The biggest problem with sharing temp tables is that it introduces external dependencies into a procedure that may not be apparent at first glance. Say you have some procedure p1 that calls a procedure p2 and a temp table #t1 is used to pass information between the two procedures. If you want run p2 in isolation to see what it does, you have to create a "harness" that defines the #t1 before you can run it. Using temp tables in T-SQL is often equivalent to using global variables in other languages-- not recommended but sometimes unavoidable.

SQL Server 2008 now has table-valued parameters but Microsoft chose to make them read-only in this release. Still, it means you don't have to use temp tables in some scenarios where you had to before.

My advice is, use them if you have to, but document their use thoroughly. If you have some proc that depends on a temp table to run, call this out in a comment.

Answer 5

As a programming paradigm it is ugly because it involves passing out-of-band parameters ('context parameters') that are not explicitly called out in the procedure signatures. This creates hidden dependencies and leads to spaghetti effect.

But in the specific context of SQL, there is simply no alternative. SQL works with data sets and you cannot pass back and forth these data sets as procedure parameters. You have few alternatives:

• Pass through the client. All too obvious not a real option.
• XML or strings with delimiters types to represent results. Not a serious option by any stretch, they may give good 'programming' semantics (ie. Demeter Law conformance) but they really really suck when performance comes into play.
• shared tables (be it in tempdb or in appdb) like Raj suggest. You're loosing the #temp automated maintenance (cleanup on ref count goes to 0) and you have to be prepared for creation race conditions. Also they can grow large for no reason (they're no longer partitioned by session into separate rowsets, like #temp tables are).
• @tables. They're scoped to the declaration context (ie. the procedure) and cannot be passed back and forth between procedures. I also discovered some nasty problems under memory pressure.

Answer 6

Another technique I've seen to avoid using temp tables is so-called "SPID-keyed" tables. Instead of a temp table, you define a regular table with the following properties:

CREATE TABLE spid_table
(
   id INT IDENTITY(1, 1) NOT NULL,
   -- Your columns here
   spid INT NOT NULL DEFAULT @@SPID,
   PRIMARY KEY (id)
);

In your procedures, you would have code like the following:

SELECT @var = some_value
FROM spid_table
WHERE -- some condition
AND spid = @@SPID;

and at the end of processing:

DELETE FROM spid_table
WHERE spid = @@SPID;

The big disadvantage of this is that the table uses the same recovery model as the rest of the database so all these transient inserts, updates and deletes are being logged. The only real advantage is the dependency is more apparent than using a temp table.

Answer 7

I guess, it is OK if the 2nd proc checks for existence of temp table & moves forward, if so. Also, it can raise error, if the temp table doesn't exist, asking user to run the proc1 first.

Why is the work divided in 2 stored procs? Can't things be done in 1 proc?

Answer 8

Sometimes this is the only way to go. If you need to do this, then DOCUMENT, DOCUMENT, DOCUMENT the facts. Here is one way to make it clear, put the table definition as a comment in the parameter section...

CREATE PROCEDURE xyz
(
     @param1  int            --REQUIRED, what it does
    ,@param2  char(1)        --OPTIONAL, what it does
    ,@param3  varchar(25)    --OPTIONAL, what it does
    --this temp table is required and must be created in the calling procedure
    --#TempXyz (RowID      int          not null primary key
    --         ,DataValue  varchar(10)  not null
    --         ,DateValue  datetime     null
    --         )
)

Also document in the calling procedure where the temp table is created....

--** THIS TEMP TABLE IS PASSED BETWEEN STORED PROCEDURES **
--** ALL CHANGES MUST TAKE THIS INTO CONSIDERATION!!!    **
CREATE TABLE #TempXyz 
(RowID      int          not null primary key
,DataValue  varchar(10)  not null
,DateValue  datetime     null
)