SELECT INTO with stored procedure result set?

Question

Post reply

SELECT INTO with stored procedure result set?

GregLyon

SSCommitted

Points: 1946
More actions
March 26, 2004 at 2:34 pm

#110821

Is there any way to Generate a temp table to hold the results of a stored procedure at the same time as executing the Stored procedure?
Id like to do something like the following which doesn't seem to work...
SELECT * INTO #tmp
EXEC (sp_FOO)

I currently need SQL 7.0 solutions today, but SQL 2000 is in the very near future for me. Table variable maybe after the upgrade?
I'm well aware that this (if it's possible) could qualify for the 'worst practices' series......but it sure would ease some early modeling and ad-hoc data requests!

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

fhanlon SSChampion Points: 13131 More actions · Answer 1

try

INSERT INTO USED_DISK

(col1,col2...)

EXEC dbo.sp_Foo

Francis

GregLyon SSCommitted Points: 1946 More actions · Answer 2

Thanks for the quick reply fhanlon,

I do use the INSERT INTO method to good effect. What I'm hoping for is something like SELECT...INTO that would generate the #tmp table at the same time as it gets the data. Of course, I'm not sure it's even possible, which I why I've posted here.

GregLyon SSCommitted Points: 1946 More actions · Answer 3

I have a couple more questions on this topic. Essentially I'd like to be able to use existing stored procedures on the fly, joined to other data. I already use the technique of building a temp table and then using INSERT...EXEC. I would like to do it without pre-building a temp table or table variable...

1) If I want to return stored procedure results to a temp table do I have to create the temp table first? Is Select Into out?

2) What about @Table Variables in sql 2000? BOL 2000 says that: SET @tablevar = EXEC(sp_MyStoredProc) doesn't work. Is there some trick to achieve similar results?

3) Am I out of luck?

Thanks in advance for your replies!

Jack Corbett SSC Guru Points: 184394 More actions · Answer 4

based on my experience you are out of luck in SQL 7. In SQL2K you would probably want to use Inline Functions which return a table based on the select statement in the function. It wouldl work something like this:

CREATE FUNCTION
dbo.test
(
/* @parameter1 datatype = default value,
@parameter2 datatype
*/ ) RETURNS TABLE
AS
RETURN ( Select col1, col2, col3 from test )

Then in an sp you would run code like this:

Select
A.col1,
B.col2,
B.col3
From
test A Inner Join
dbo.Test() B On
A.col1 = B.col1

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

GregLyon SSCommitted Points: 1946 More actions · Answer 5

Thanks Jack,

As it turns out I'll be moving to SQL 2000 very soon now, so this will become useful almost immediately. I'm running tests this week.