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
 General SQL Server Forums
 New to SQL Server Programming
 proc results in to table

Author  Topic 

aakcse
Aged Yak Warrior

570 Posts

Posted - 2010-05-24 : 11:45:19
Can we take the results we get from sproc into a table ( or a temp table) in a single exe statement, if yes then how?



-Neil

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2010-05-24 : 11:49:26
Not a single statement, but you can put it into a #temp table easily enough. First create your table and then:

INSERT INTO #tmp EXEC usp_yourproc
Go to Top of Page

aakcse
Aged Yak Warrior

570 Posts

Posted - 2010-05-24 : 12:02:44
this is not working
INSERT INTO #tmp EXEC usp_yourproc
getting error at insert into.

if not possible in a single stm it is fine. but I need to execute this for more than one proc and dont know how many columns these proc will be having.


insert into #temp exec proc1


-Neil
Go to Top of Page

aakcse
Aged Yak Warrior

570 Posts

Posted - 2010-05-24 : 12:05:04
create procedure test
as
begin
select 1
end

insert into #t exec test

Msg 208, Level 16, State 0, Line 1
Invalid object name '#t'.


-Neil
Go to Top of Page

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-05-24 : 12:05:52
What is the error that you received?
Go to Top of Page

aakcse
Aged Yak Warrior

570 Posts

Posted - 2010-05-24 : 12:06:20
I may be having differnt number of columns each time in different different procs, this is just to test I have taken one column

-Neil
Go to Top of Page

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-05-24 : 12:14:06
You cannot just use
INSERT INTO <table> exec <sp>

for all stored procs. The number of columns returned from the SP should match the number of columns in the table.

From your error above, did you create #t first before the insert?
Go to Top of Page

aakcse
Aged Yak Warrior

570 Posts

Posted - 2010-05-24 : 14:04:00
Hi Vijay,
If I create it will work, but every time the number of colums will keep changing from procedure to procedure..how to handle this.



-Neil
Go to Top of Page

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-05-24 : 14:15:33
I'm not sure what you are asking. Is there a reason you want to create a temp table and store the results of all SPs.

Maybe you can explain your actual requirement and we can think of a better solution.
Go to Top of Page

aakcse
Aged Yak Warrior

570 Posts

Posted - 2010-05-24 : 14:32:23
I appreciate your efforts in helping me.
We have many procedures,who's output is to be stored in a temp table individually and perform certain action on the data. this is the only info I have from my lead.

Regards,

-Neil
Go to Top of Page

zstarsales04
Starting Member

20 Posts

Posted - 2010-05-25 : 02:35:28
spam removed
Go to Top of Page

basicconfiguration
Constraint Violating Yak Guru

358 Posts

Posted - 2010-05-25 : 02:41:02
quote:
Originally posted by zstarsales04

Hello, everybody, I am new here. Here is something might be helpful for you.www.zstar.hk www.edgei-ds.cn (many movies to download)www.tigersupermall.com




We are being attack. Run!!
Go to Top of Page

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2010-05-25 : 04:19:20
quote:
Originally posted by aakcse

I appreciate your efforts in helping me.
We have many procedures,who's output is to be stored in a temp table individually and perform certain action on the data. this is the only info I have from my lead.

Regards,

-Neil



As I said in my very first post, you need to create the table first. As asked elsewhere, why would you want to do this for every result set? Why not just return it to the front end and work on the results there if you want a generic way of returning?
Go to Top of Page
   

- Advertisement -