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. If anyone has a good solution, please leave a message.