Its not working anyway.
insert #temp exec revertMoves @billHall, @startdate, @enddate, @periodid

Gives me this error:

Invalid object name '#temp'.

Is there any other way to do this?



>IMO, you have to look at it proc specifically. I generally use functions to isolate complex functionality, especially if it is re-used throughout the application. For example, in one project we stored all times in GMT and converted it to the users selected timezone. We did this with a function that took the user's ID, looked up their timezone, and did the appropriate calculations.
>
>All communications between the client and the database should still be through stored procedures, IMO.
>
>-Mike
>
>>I ran across this in the BOL. I thought I seen someone asking about it before.
>>
>>
>>This topic describes how to determine whether to rewrite existing stored procedure logic as user-defined functions. For example, if you want to invoke a stored procedure directly from a query, repackage the code as a user-defined function.
>>
>>In general, if the stored procedure returns a (single) result set, define a table-valued function. If the stored procedure computes a scalar value, define a scalar function.
>>
>>Criteria for Table-Valued Functions
>>If a stored procedure meets the following criteria, it is a good candidate for being rewritten as a table-valued function:
>>
>>The logic is expressible in a single SELECT statement but is a stored procedure, rather than a view, only because of the need for parameters. This scenario can be handled with an inline table-valued function.
>>
>>
>>The stored procedure does not perform update operations (except to table variables).
>>
>>
>>There is no need for dynamic EXECUTE statements
>>
>>
>>The stored procedure returns one result set.
>>
>>
>>The primary purpose of the stored procedure is to build intermediate results that are to be loaded into a temporary table, which is then queried in a SELECT statement. INSERT...EXEC statements can be written using table-valued functions. For example, consider the following sequence:
>>
>>INSERT #temp EXEC sp_getresults
>>SELECT ...
>> FROM #temp, t1
>> WHERE ...