Exec stored procedure into dynamic temp table
sql, sql-server, stored-procedures, t-sql
Solution
SELECT * INTO #TempTable
FROM OPENROWSET
('SQLNCLI','Server=(local)\SQL2008R2;Trusted_Connection=yes;',
'EXEC OtherDb.DataProd.abc')
SELECT * FROM #TempTable
Problem
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.