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