介绍：in this example,Pandora Red Bracelet 783, caller generates the temp table,Pandora Murano Glass Bead 110, and called_procedure fills in in. however it could also be the situation that caller fills the table with input info, that called_procedure then employs for some processing.



there exists a likely servicing problem right here: if
called_procedure is named from a lot of spots,Pandora Black Set 088, and you desire to adjust what columns that called_procedure read/writes, you have to revisit all calling stored procedures to edit the temp-table definition. for this reason, sharing temp tables is generally useful whenever you have a solitary pair of caller and callee. if there are several callers, making use of a process-keyed table might be a much better alternative. on the other hand, in the event the temp is small, maybe only just one column of consumer ids to method, the table is probably to get extremely stable. use your own personal excellent judgement to ascertain what on earth is greatest for you personally.


but a possibility was advised to me by richard st-aubin. the callers would make the temp table with a single dummy column,Pandora Bangle With Yellow Glass Beads And Silver Charms 051, and then get in touch with a stored procedure that employs alter table to create the real columns. this process could be the solitary holder with the temp table definition. (you can not place the create table in the stored process, as in that circumstance the temp table goes absent once the process exits. using alter table evades the problem.) this approach can definitely be really worth discovering, but i wish to create a phrase of caution. adding columns to a table at run-time can lead to surprising problems in the event the process is recompiled. if you phone the treatment immediately following the generate table statement,Pandora Murano Glass Bead 064, you ought to be rather protected. but this depends upon the great print in sql server, so it could break using a new launch. (and in any situation, it does not perform with sql 6.5.)


(a more advanced way to tackle the routine maintenance problem is to use a pre-processor, and set the definition with the temp table in an consist of file. in the event you possess a c compiler around, you are able to use the c pre-processor. my abaperls includes preppis which we use in the system i spend most of my time with.)


there is an interesting probability for flexibility here. the callee only has to care about the columns it reads or writes. this permits a caller to add extra columns for its individual usage when it generates the temp tables, and two callers to the same callee could have different definitions in the same temp table.


you should be aware of that there is a achievable performance issue with using temp tables and that is recompilation. this might be a serious problem on sql 7 and sql 2000. each time the caller is invoked, a brand new instance in the temp table is created, and for this reason the callee will be recompiled (as the table actually could have a new definition). the cost for a recompilation of a method having a solitary select that is moderately complex,Pandora Bangle With White Glass Beads And Silver Charms 018, may be less than 200 ms. a longer procedure of 100-200 lines of code may take over a second to compile. in the event the method itself needs a minute to run, this is no big concern. but in the event the method as soon as compiled executes in sub-second, that recompilation second will be very noticeable to end users. so this is an important issue that you have to consider before you choose this method.


in sql 2005 your situation is different, as it does recompilation on assertion level. this means that only the statements that refer to the shared temp table will probably be recompiled. thus, a callee that accepts input info right into a temp table, could copy that information right into a local temp table or a table variable, and only that statement could be recompiled, and that is probable to become cheap. likewise a callee that fills in output data, only wants to refer to the shared table in a final insert assertion.


right here i only discussed recompilation simply because the temp table can be a new table each time. temp tables can also result in recompilations as a result of auto-statistics,Pandora Pink Bracelet 727, something that applies in general whenever you use temp tables, not only if you share them between stored methods. i return to the topic of auto-statistics when i look at process-keyed tables.


if you would like to reuse an existing procedure by sharing a temp table, you do as for a table-valued function: you move to the core to some common method,Pandora Silver Pendant Gray Charm 232, and transform the existing process right into a wrapper so that client-side programs are unaffected.