Stored procedure Exec in the dynamic temporary table

advertisements

To my knowledge; what I want to do is not possible in sql, but it is worth asking you guys.

Lets say I have a stored procedure abc that returns columns Id and Value. This stored procedure is mainly being used by other departments for functional reasons and I will only use it every now and again for data checks.

So using it as part of my stored procedure:

DECLARE @tABC TABLE
(
   ID      INT,
   Value   DECIMAL(12,2)
)

INSERT INTO @tABC
   EXEC OtherDb.DataProd.abc

Oky so this will work perfectly for now, but what if they change the structure of their stored procedure?

Adding or removing a column from their stored procedure will break my code, so is there a way of making my code more flexible.

My last desperate attempt went something like this:

WITH tempTable AS
(
    EXEC OtherDb.DataProd.abc
)
SELECT ID, Value FROM tempTable

Which obviously failed miserably.


SELECT * INTO #TempTable
FROM OPENROWSET
('SQLNCLI','Server=(local)\SQL2008R2;Trusted_Connection=yes;',
     'EXEC OtherDb.DataProd.abc')

SELECT * FROM #TempTable