Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Exec stored procedure into dynamic temp table

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.

like image 593
JAT Avatar asked Nov 29 '13 07:11

JAT


People also ask

How do I insert stored procedure results into temp table in SQL?

You don't need anything that fancy, just copy and paste into your SQL editor. Use the column names, sizes, and types to construct a "Create table #x [...]" or "declare @x table [...]" statement which you can use to INSERT the results of the stored procedure.

Can we access a temp table of one stored procedure from another stored procedure?

The table can be referenced by any nested stored procedures executed by the stored procedure that created the table. The table cannot be referenced by the process that called the stored procedure that created the table."

Can we use temp table in stored procedure SQL Server?

Stored procedures can reference temporary tables that are created during the current session. Within a stored procedure, you cannot create a temporary table, drop it, and then create a new temporary table with the same name.


Video Answer


2 Answers

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

SELECT * FROM #TempTable
like image 137
Raj Avatar answered Oct 17 '22 21:10

Raj


Insert into a temp table. I know this works in 2008 and above, not sure about 2005. Your temp table columns must match your Stored Proc columns.

create table #mytable (custid int,company varchar(50),contactname varchar(50)
                , phone varchar(50),address1 varchar(50)
                , address2 varchar(50),city varchar(50)
                ,st varchar(2),zip varchar(20))

insert into #mytable (custid,company,contactname,phone,address1,address2,city,st,zip)
exec dbo.sp_Node_CustomerList_wService @segid = 1

select * from #mytable
where custid = 5

drop table #mytable
like image 31
Tim Melton Avatar answered Oct 17 '22 20:10

Tim Melton