Simon popped over earlier to ask if I knew a way to capture the data within a SELECT returned as part of a stored procedure within TSQL. So, the item he was after wasn't an output variable, neither was it the return value. I remembered roughly how to do it, and then hunted through some of our TSQLUnit tests to find an example to send to him. I'd used temporary tables to do it. I wondered why I'd use that instead of a table variable, so I thought I'd write a bit of code to find out. The reason was that we got the error "EXECUTE cannot be used as a source when inserting into a table variable."

CREATE TABLE MyTest
(Test1 VARCHAR(20))

INSERT INTO MyTest
 (Test1)
VALUES
 ('Hello')

CREATE PROCEDURE GetTest
AS
BEGIN
  SELECT Test1
  FROM MyTest
END

DECLARE @tabCaptureTheResults TABLE (Data VARCHAR(20))

INSERT @tabCaptureTheResults
EXEC GetTest

This results in:
Server: Msg 197, Level 15, State 1, Line 4
EXECUTE cannot be used as a source when inserting into a table variable.

CREATE TABLE #CaptureTheResults (Data VARCHAR(20))

INSERT #CaptureTheResults
EXEC GetTest

SELECT * FROM #CaptureTheResults

This results in 'Hello' being returned, exactly as required.
Whilst this works, it isn't exactly going to be efficient. Anyone know of any better ways of doing this within TSQL?

UPDATE:
As of SQL 2005 both cases seem to work - Hurrah!