Calling Stored Procedure (SP) inside another SP

Question

Post reply

Calling Stored Procedure (SP) inside another SP

Narayan LS

SSC-Addicted

Points: 412
More actions
November 19, 2009 at 3:00 am

#99961

I have one SP 'A' and another SP 'B'. SP 'B' is returning three tables. I want to call SP 'B' inside SP 'A' and want to fetch all three tables.
Please help to achive this.

Viewing 9 posts - 1 through 8 (of 8 total)

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

Paul White SSC Guru Points: 150468 More actions · Answer 1

What do you mean by 'fetch'?

If you are clear with the question, I'll be clear with the answer 🙂

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

Narayan LS SSC-Addicted Points: 412 More actions · Answer 2

I mean by retrieve. Getting the record which that SP is returning.

Paul White SSC Guru Points: 150468 More actions · Answer 3

Ok let me try to guess.

Procedure B has three different SELECT statements, each of which return a result set to the output window when run from SSMS.

What is it exactly that you want to do with these result sets in Procedure A? Do you want to put them into a table? Process them somehow? Return them to the caller? Join them together? Pivot them? Detail please.

An example would help, if you can spare the time.

Paul

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

vihi.koyott SSC-Addicted Points: 483 More actions · Answer 4

Hi,

Is you second procedure returning same number of columns for all the three tables ?

I have provided an example over here assuming that Storedprocedure B returns equal number of columns for all the three columns.

.It however returns your entire result as a single table

create procedure [SprocA] ----------------your parent procedure A

as

begin

declare @temp_table table

(

col1 varchar(50),

col2 varchar(50),

col3 varchar(50),

...........

.....

coln varchar(50)

)

---------Do your calculation--------

insert into @temp_table (col1,col2,col3,col.......,coln) exec SprocB ------------ call your child procedure B

select * from @temp_table ---------------this returns your result as one table.

end

Narayan LS SSC-Addicted Points: 412 More actions · Answer 5

It is like SP 'A' is having some table and other things. Inside SP 'A' i want to call Exec SP 'B' and want to get the data which all the there tables are returning while executing.

The no. of columns in all the three tables are not same.

First table will have three column, second table will have 10 column and third table will have some other number.

I don't have any JOINS in that.

Hope this is enough to understand.

vihi.koyott SSC-Addicted Points: 483 More actions · Answer 6

The above code will work for that.

only u'll have to declare the temporary table variable with number of columns = maximum number of columns returned by ur three tables.

You'll get ur final result (of SP 'A') as a single table. Is that what u want ?

Narayan LS SSC-Addicted Points: 412 More actions · Answer 7

Yup, it works if the number of columns should be same in all the tables. But we want the solution like getting the particular table data while executing the SP 'B' and inserting into the temp table. If that is there then it would be great.

vihi.koyott SSC-Addicted Points: 483 More actions · Answer 8

Phew !!! pls provide a sample code..can't help u much w/o that.

Btw the above code will work even if the number of columns are not equal.

What i meant was :

Eg if ur SP 'B' retruns 4 tables :

table 1 has 3 columns

table 2 has 5 columns

table 3 has 2 columns

table 4 has 10 columns

then declare the temporary table variable as table variable having 10 columns.