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.
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.
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."
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.
SELECT * INTO #TempTable
FROM OPENROWSET
('SQLNCLI','Server=(local)\SQL2008R2;Trusted_Connection=yes;',
'EXEC OtherDb.DataProd.abc')
SELECT * FROM #TempTable
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With