The use of another stored procedure as a table in one stored procedure does not support the direct receipt of a stored procedure from the from in SQL Server. This time we can use the temporary table to achieve.
Example:
SELECT ID, Roleid, roletype into #TempPurviewTableContainer from Systempurview WHERE 1=0
INSERT #TempPurviewTableContainer exec pmanage_getpurview @Type, @Id
SELECT * FROM #TempPurviewTableContainer
It should be noted that this method cannot be nested. For example:
Procedure A
Begin
...
Insert #table EXEC b
End
Procedure B
Begin
...
Insert #table EXEC C
SELECT * FROM #table
End
Procedure C
Begin
...
SELECT * FROM SomeTable
End
Here is the result set of a tune B, and B has this application B tuned the result set of C, which is not allowed, will be reported "INSERT EXEC statement can not nest" error. In practical application, this kind of application should be avoided.