Separates temporary tables between procedures that are considered bad?

Question

I have 2 procedures. One that creates a temporary table and another (or several others) that use the temporary table created in the first proc. Is this considered a bad form? I am now entering the existing codebase and use this template a lot. It certainly bothers me, but I can’t say that this is clearly a bad idea. I just find it an annoying pattern - something smells rotten, but I can't say that. I would prefer to build the table locally and then populate it with exec. But this requires procs that return only one table, which is unusual in the existing code base.

Are SQL gurus shying away from this kind of sharing pace? If so, why? I am trying to formulate an opinion about this and would like to contribute.

Will the prospect be a viable alternative?

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

All methods are well discussed here: http://www.sommarskog.se/share_data.html

Answer 1

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

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

• Go through the client. Everything is too obvious that this is not a real option.
• XML or strings with separator types to represent the results. This is not a serious option for any stretching, they can give good “programming” semantics (ie Demeter Law ), but they really do suck when performance comes into play.
• Shared tables (whether in tempdb or appdb), as Raj suggests. You lose the automatic #temp service (cleaning by the number of links goes to 0), and you should be prepared to create race conditions. In addition, they can grow large for no reason (they are no longer partitioned by the session in separate rows, for example, #temp tables).
• @tables. They are tied to the context of the declaration (i.e., to the procedure) and cannot be transferred between procedures. I also found some unpleasant problems under pressure from memory .

Answer 2

The biggest problem with temporary exchange tables is that it introduces external dependencies into the procedure, which may not be obvious at first glance. Say you have procedure p1 that calls procedure p2, and temporary table t1 is used to transfer information between two procedures. If you want to run p2 in isolation to see what it does, you need to create a “wiring” that defines # t1 before you can start it. Using temporary tables in T-SQL is often equivalent to using global variables in other languages ​​- not recommended, but sometimes inevitable.

SQL Server 2008 now has tabular options, but Microsoft decided to make them read-only in this version. However, this means that you do not need to use temporary tables in some scenarios where you had to.

My advice, use them if you need to, but carefully document their use. If you have a proc that depends on the current temp table, call this in the comment.

Answer 3

Sometimes this is the only way. If you need to do this, DOCUMENT, DOCUMENT, DOCUMENT facts. Here is one way to make this clear: put the table definition in a comment in the options 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 the 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 ) 

Answer 4

I ran into the exact same problem. What can I say from personal experience:

If there is a way to avoid using the #temp table for multiple procedures, use it. # temp tables are easy to lose and can easily grow tempdb if you are not careful.

In some cases, this method cannot be prevented (my classic example is a certain reporting function that builds data in different ways based on the configuration of the report). If you are carefully managed, I find that acceptable in these situations.

Answer 5

Sharing temporary tables is a good idea. But we must ensure that table-level data should not be processed by two processes at the same time, leading to a Dirty Read / Write script. It will also help to have a separate table [centralized] and handlers working on it to synchronize data. In terms of performance, it’s true that with multiple processes using the same temporary data, performance will decrease. But at the same time, having separate tables [on Proc] will increase memory consumption,

Answer 6

I have used this approach on several occasions. But I always declare the temp table as a fully qualified table, and not using #tablename.

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

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

Rajah

Answer 7

Another method I have seen to avoid using temporary tables is the so-called "SPID-keyed" tables. Instead of the 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 will 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 that all of these temporary inserts, updates, and deletes are logged. The only real advantage is that the dependency is more obvious than using the temp table.

Answer 8

I think this is normal if the second proc checks for the existence of a temporary table and moves forward if so. In addition, it may cause an error if the temporary table does not exist, asking the user to run proc1 first.

Why is work divided into 2 stored processes? Cannot be done in 1 proc?