The select won't return the results to your current SSMS window. The system will run the sql statements in their seperate SPIDs. So you'll see nothing.
The typical scenarios is like this -- You can create a table (or a global temp table ##tmp), and make the stored procedure to insert their partial results to the table. After all partial works have been completed, use select * from the table to get the combined results.
jxhopper (5/18/2014)
For example... If I create the following stored procedure:create procedure test
as
select * from sys.databases waitfor delay '00:00:30'
and then run this:
declare @rc int
exec @rc=sp_exec_init 8
if (@rc=0) begin
exec sp_exec 'exec test'
exec sp_exec 'exec test'
exec sp_exec 'exec test'
end
exec sp_exec_end
It just runs and doesn't do anything. No error, no activity. Can you not call another stored procedure as the sql statement?