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:

<code>
    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
</code>

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:

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


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 just wondered what benefits the second method gives.  Performance? 

Or is there a third method?
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:

<code>
    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
</code>

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:

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


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 just wondered what benefits the second method gives.  Performance? 

Or is there a third method?