Returns recordset from EXEC stored procedure

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)

Returns recordset from EXEC stored procedure

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-08-26 : 08:12:59

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

483 Posts

Posted - 2005-08-26 : 08:41:33

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-26 : 08:57:37

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources