Inserting Stored Procedure results into temp table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Inserting Stored Procedure results into temp table

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-01-28 : 08:45:29

Todd writes "Hi. Thanks for taking my question.

create procedure dbo.StoredProc2TempTable
(
@StoredProcedureNameAndArguments varchar(8000)
)
select *
into #NewTempTable
from
exec(@StoredProcedureNameAndArguments)

does not work.

I just need to use the results of a stored procedure
as the intermediary result in another stored procedure
(in a join)

Thanks and good luck.
Please email me the answer as well as post up here."

shankarc
Starting Member

37 Posts

Posted - 2002-01-28 : 09:40:59

Have you tried storing the Stored Proc result in a variable and then inserting in to the temporary table?

declare @res int
select @res = exec(@StoredProcedureNameAndArguments)

and then your insert statement. Make sure that the variable holds the correct syntactic statement. Try running the stored proc alone first and then try inserting the result.

ToddV
Posting Yak Master

218 Posts

Posted - 2002-01-28 : 16:13:50

You have two choices (maybe more). You can use an Output parameter.

Create Procedure test1 @Param1 INT Output
AS

SET @Param1 = 1
RETURN
GO

--You can use an Output parameter.
DECLARE @Param1 INT
EXEC test1 @Param1 = @Param1 OUTPUT

SELECT "@Param1" = @Param1

OR
Create Procedure test2 AS

SELECT 1
RETURN
GO
CREATE TABLE #TEMP (
Param1 INT)

INSERT #TEMP
EXEC test2

SELECT * FROM #TEMP

Good Luck

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources