Calling a stored procedure from another stored procedure

0
votes

I have two fairly large and complex stored procedures. I want to call a second stored procedure from the first stored procedure. For example:

-- stored_procedure_one
select tb1.col1, tb1.col2, sp1.col3, sp1.col4
from table1 tb1
inner join stored_procedure_two sp1 on sp1.col1 = tbl1.col1

Is something similar possible with SQL as the above script gives me an invalid object error message.

Using a temp table is not good in this example, because if I did that, it would take an hour just to fill the temp table with all the data from the second stored procedure. I only want the stored procedure to return the needed data.

2
sqlsql-serversql-server-2008tsqlstored-procedures
Do you really need all 50 million rows or some summed output records based on certain ids? You should probably rethink your query and create one that is more suited for this particular case than try to mingle two seperate queries into some frankenstein query because you didn't want to refactor. The time spent could be worth it. - Kevin Cook

2 Answers

3
votes

This is not going to work. You cannot join on a stored procedure. However, you could consider changing stored_procedure_two into a table-valued user defined function. You could then 'join' via a Cross Apply. I have done this on numerous occasions and it works quite well.

If the second stored procedure is too large and complex, it may not be possible to convert to a UDF. In this case, I think your only alternative is to save the results of the second stored proc to a table and join on that. But that could be somewhat inefficient and messy.

0
votes

You may add the results of the second procedure in a local variable table

DECLARE @Table TABLE
(
  Col1 int,
  Col2 ...
)

INSERT INTO @Table
EXEC stored_procedure_two

select tb1.col1, tb1.col2, sp1.col3, sp1.col4
from table1 tb1
inner join @Table tbl2 on sp1.col1 = tbl1.col1