SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Returns recordset from EXEC stored procedure
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts

Posted - 08/26/2005 :  08:12:59  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote
ActuarialDBA writes "I have a stored procedure "stored_proc_name" which returns a recordset with hundred of columns. Right now, I have a query like this:

CREATE TABLE temp_table(col1, col2,...,col100...)

INSERT INTO temp_table
EXEC stored_proc_name

SELECT TOP 10
FROM temp_table, another_table

DROP TABLE temp_table

Are there any other ways to avoid the first step of creating the temporary table? I'm asking this because sometimes structure of the recordset from the stored_proc_name is unknown, or too much to write them all out, not to mention about the performance side effects. THANKS."

Thrasymachus
Constraint Violating Yak Guru

USA
483 Posts

Posted - 08/26/2005 :  08:41:33  Show Profile  Reply with Quote
that's not a temp table. temp tables are prefaced with #. we can probably optimize this for you but we are going to need to see what your stored proc does, we will need the table definitions and some sample data.

====================================================
Regards,
Sean Roussy

Thank you, drive through
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22713 Posts

Posted - 08/26/2005 :  08:57:37  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
You cannot do

Select * into #myTable from exec sp

You need to create the temp table and then insert it as what you did
Is it necessary to have 100 columns?
Where do you want to show them all?
I think you need to normalize the requirement
As said explain your requirement clearly

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000