SQL Server saves the dataset returned by the stored procedure to a temporary table

Project requirements: In my stored procedures, you need to call the stored procedures written by others in the group and obtain the dataset returned by the Stored Procedure for joint query with my own data.

This stored procedure includes input and output parameters, and contains the insert temporary table operation.

 

I tried several solutions on the Internet, but none of them worked:

1. insert into # tmptable exec pro

Insert statements cannot be nested]

2. insert into # tmptable

Select * From OpenRowSet ('sqlncl', 'driver = {SQL Server}; server = [servername]; uid = [user name]; Pwd = [Password]; database = [database name], 'Clare @ t int exec DBO. pro

''Cy '', @ t output ')

[The object "Exec DBO. Pro 'cy ', @ t output'" cannot be processed '". The ole db access interface "sqlncli" of the linked server "(null)" indicates that this object does not have a column or the current user does not have the permission to access this object .]

 

If any expert has any solutions, please kindly advise. Thank you first !!!