How to call or execute a Stored Procedure from inside a Select Statement

Some times you have a requirement as you need to call a stored procedure in side another stored procedure and you need to use the returned values.That time you need to create a temp table and executing that stored procedure insert that results in to temp table like below.

INSERT INTO @myTable   
 EXEC sp_myStoredProcedure  

But some times you will face some issues like the proc. is nested the following error occurs : An INSERT EXEC statement cannot be nested. To avoid this type of errors we can use openrowset. It is really simple to call a stored procedure from a select statement Using OPENROWSET. Below TSQL Query calls the procedure sp_who from the select statement.

select * from openrowset('SQLNCLI','DRIVER={SQL Server};Server=****;uid=*****;pwd=****','Exec DBName..SPName params'  

For more information you can follow this URL Check this link

If the openrowset is disabled in the system, execute the below commands

  
 exec sp_configure 'show advanced options', 1  
 RECONFIGURE;  
 exec sp_configure 'Ad Hoc Distributed Queries', 1  
 RECONFIGURE;