Stored Proceedure to temporary 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)

Stored Proceedure to temporary table

Author

Topic

Kytro
Starting Member

9 Posts

Posted - 2005-10-13 : 17:10:55

I am trying to use a stored procedure (written by someone else) that creates a pivot table and modify the results after putting them into a temporary table.

I have found, however no way to take the (dynamic) results of a stored procedure and put it into a table.

EXEC results can't be put into a table variable.
You cannot SELECT * INTO #Temp_Table from EXEC.

You can INSERT INTO EXEC, but you need an existing table, rather than a dynamic one. How can this be done?

nathans
Aged Yak Warrior

938 Posts

Posted - 2005-10-13 : 19:35:17

You are trying to access a temp table from the scope in which the proc executes... which is why youre having difficulty. Ive seen some people use dynamic sql or the loopback openquery method:

-- this example from TechNet

-----------------------------
-- create linked server
-----------------------------
DECLARE @provstr varchar (2000)
SET @provstr = 'PROVIDER=SQLOLEDB;SERVER=' + @@SERVERNAME
EXEC sp_addlinkedserver 'loopback', @srvproduct = 'MSSQL', @provider = 'SQLOLEDB', @provstr = @provstr

-----------------------------
-- create a test proc
-----------------------------
use test2
go

create procedure dbo.usp_test
as
set nocount on
select 	1 as test_id,
	'Nathan' first_name,
	'Skerl' last_name
go

-----------------------------
-- Create and populate table from Exec
-----------------------------
select * into #yourtemp from openquery (loopback, 'exec test2.dbo.usp_test')

-----------------------------
-- return results
-----------------------------
select * from #yourtemp


drop proc usp_test
drop table #yourtemp

Nathan Skerl

Kytro
Starting Member

9 Posts

Posted - 2005-10-13 : 22:00:06

Thanks! I feel like I am getting closer. I am using a temp table and passing it as a parameter to the stored procedure, but it does not seem to exist if I call openquery

Kytro
Starting Member

9 Posts

Posted - 2005-10-14 : 00:20:31

Does anyone know how to create a dynamic pivot table witha SQL function that returns a table?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources