Should you use temporary tables to pass data between stored procedures?

I have a number of search functions (stored procedures) which need to return results with exactly the same columns.

This is the approach that was taken:

Each stored procedure had the general structure:

CREATE TABLE #searchTmp (CustomerID uniqueIdentifier)

INSERT INTO #searchTmp
SELECT C.CustomerID FROM /**** do actual search here, based
                              on stored proc arguments ****/

EXEC spSearchResults

DROP TABLE #searchTmp

In the above, spSearchResults use the #searchTmp table in a select. spSearchResults would always return a table with the same columns, and had quite a few joins.

However, rather than use a temporary table, is the following approach more acceptable:

SELECT col1, col2, col3, col4, .... etc, lots of columns ... FROM table1 LEFT JOIN table 2 ON ... etc, lots of joins ... WHERE ... DO ACTUAL SEARCH HERE ...

If there are 10 different searches to do (eg. search for a customer based on postcode, one search based on surname etc.), this second approach means there is lots of duplication of columns and joins specified. If the code that uses the search functions changes such that a new column is needed to be returned, there's then 10 stored procedures that need to be updated.

I'm all in favour of the first method, but i ju开发者_开发知识库st wondered what benefits the second method gives. Performance?

Or is there a third method?

As long as the temp table contents represent the final set of unique keys to be output, and no further trimming of the resultset is done afterwards, then it is a very efficient way of implementing your requirements.

Problems will only arise if the temp table contains an intermediate set of keys which is cut down further in subsequent queries. In that scenario, reading all the data in one query will be more efficient.

EDIT: As Mark says, there may be a performance difference due to the query optimiser being able to use multiple threads in the single query approach, but not in the temp table approach. You have to weigh this potential gain against the vast improvement in maintainability of the temp table approach, and decide which is more important for you. As usual with database questions, it's better to measure performance rather than guess at it.

The second should perform better. It will the smaller proportion of the data that you want to return.

The second gives the query optimiser the chance to do the query in any order it wants, the first forces a select from the first table first (which in your case is likely to be the one wanted).You can also use parallel queries on the second one ie more than one thread working on the query as the optimiser can do this.

To check on this run a Showplan (Sybase or SQL Server) or EXPLAIN (Iracle) etc to see the actual query generated.

If using Microsoft Sql Server, I prefer a third method:

Create Function GetSearchKeys([Search parameters here])
Returns @Keys Table (pk Int Primary Key Not Null)
As
Begin
      Insert @Keys(pk)
      Select C.CustomerID 
      From /**** do actual search here, based 
               on Search parameters ****/
      Return
End

  -- ----------------------------------------------------

And then, in each stored proc,

  SELECT col1, col2, col3, col4, .... etc, lots of columns ... 
  FROM table1 
     LEFT JOIN table 2 
        ON ... etc, lots of joins
     Join schema.GetSearchKeys([Search parameters here]) K
        on K.pk = [whatever table.column has the primary key in  it]