Way to Query a stored procedure???
-
I am trying to create one stored procedure that would use data generated from another stored procedure. Is this possible?
Create Proc 2 as
--need to execute the other stored proc into a temp table or table variable here
exec sp_Get1 into tmpTable
Select *
from tmpTable
--I am going to do more with the data, this is just for arguments sake.
-
INSERT INTO tmpTable exec sp_Get1
So long as tmpTable is either a real table or a "normal" #tmpTable that works. EXEC isn't compatible for INSERTing into variable based @tmpTables, though.
Alternatively you could pull the content from the sp_Get1 into a function that returns a table. sp_Get1 would the be SELECT * FROM dbo.TheFunction() and sp_Get2 would be INSERT INTO tmpTable dbo.TheFunction(). Just in case you wanted to complicate matters more.
-
yes but the temp table layout must match what the proc is going to spit out:
create proc itest
as select id, name from sysobjects
GO
--1 create temp table
select id, name into #t from sysobjects where 1=2
--2 fill it from proc results
insert into #t EXEC itest
-
You can't nest these sorts of queries either.
If proc1 contains an INSERT...exec then you can't have a procedure that inserts the results of proc1.
-
I tried the "SELECT * FROM dbo.TheFunction()" approach and can never get it to work. I get an "Invalid object name" error. I know that the object is valid because if I exec the object it works.
...JS
-
Have you a specific example?
Can you post the code for the function?
Have you specified the arguments?
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply