SP resultset to a table

Question

SP resultset to a table

Ramesh Saive

SSC-Insane

Points: 24275
More actions
September 11, 2006 at 7:21 am

#119452

Hey all,
I just want to know that is there any way to have a table created with the output of a stored procedure?

--Ramesh

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

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

Sreejith Sreedharan SSCertifiable Points: 5403 More actions · Answer 1

Do you want the output of an SP to populate a table? If so why can't you populate a table as part of sp and query the table when sp is completed.

Or have you looked at insert into tableName...exec sp.

Hope this gives you some idea.

Thanks

Sreejith

SQLZ SSChampion Points: 12872 More actions · Answer 2

Hi Ramesh,

The answer is, no. You have to create the table first and then populate it like so:

insert

into my_table ([column list]) exec my_procedure

Hope that helps,

Karl
source control for SQL Server

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

Well you don't absolutely have to but I'd suggest that solution to extreme problems only.

Are you able to create the table before running the SP??

If not then why not??

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 4

Let me give some more information about the thread...

Currently, we're having an SP which gives us paged data by dynamically building the SQL. This SP takes table name , column list, page size , page number etc. as input parameters & according builds the SQL & returns the paged data.

I want to modify this SP to work on the resultset of a stored procedure. This is because, we want to use this SP in all the pages for listing....

Currenly we've done the paging in the page itself. Now we want it to be done in the RDBMS itself.

As explained, in this scenario , we'd never know what the output of a stored procedure & thats why we cannot create a predefined table to populate data.

If this is not possible then what are the alternatives to achieve this task?

--Ramesh