You would have to insert the output of the inner SP into a temp table, and then access the temp table.

Something like:

create proc myoutperproc()

as

begin

.....

INSERT #MyTempTable

Exec MyInnerProc

Select...

It never gets that far. The error you're seeing happens before you even get to the GOTO.

Since you're in 2005 - you REALLY should try the NEW way to...

Fraggle (9/30/2008)

---

Matt, I agree with you. I have never seen a PIVOT help in performance, but I hestitate to say that this will always be the case. When...

What's the clustered index on this table? None of these are.

I disagree with Fraggle - I haven't seen PIVOT generate any perf gains. As a matter of fact - given its limitations, it really hasn't been of much use,...

Perry Whittle (9/30/2008)

---

Matt Miller (9/30/2008)

---

As to the clustered index - rebuilding a clustered index = rewriting an entire table, so it's not out of the question that the file would...

You might care...

Actually - it's deprecated. Right now. If you try that syntax against a SQL Server 2005 or higher, with compatibility @ 90 or above, you get:

Msg 4147, Level...

The sub-query needs a GROUP BY clause. As in -

UPDATE OrderDetails

SET

ShipID...

Agreed - it looks to be fairly accurate. Considering he does try to run the stuff in 2005 at the end - he didn't mention that SQL 2005 can...

rbarryyoung (9/30/2008)

---

Aye, there's the rub.

The other problem with both of these tricks is that they rely on creating and using a second SQL process thus fooling SQL Server about the...

Yup I have.

I haven't yet managed to get SYSTEM stored procs to work this way, but that's something having to do with OPENQUERY (it just keeps telling me...

All right - no problems:

Assume a stored proc like the following:

Create PROCEDURE matt

AS

BEGIN

SET NOCOUNT ON;

SELECT top 20 * into #matttbl

from Tally

select * from #matttbl

END

(not incredibly smart, but it does...

If you allow for using OPENQUERY, it gets a whole lot easier....

Again - just like michael mentioned - it's a bad idea. But you can do it under some...

If by "work space you don't get back", you mean "space that is used temporarily, marked as free in the data file once completed, to be used in the future...