How to Use the result set returned by another stored procedure in a stored procedure

How to Use the result set returned by another stored procedure in a stored procedure

Other descriptions of the same nature as this issue include:
How can I retrieve the execution results of dynamic SQL statements in the stored procedure?
How can I implement a function similar to select * from (Exec procedure_name @ parameters_var) as datasource where?
Procedure_name is the name of a stored procedure, and @ parameters_var is the process parameter.
How to pass the execution result set of a stored procedure to another stored procedure?
In a stored procedure, how does one select an execution process based on the execution results of another stored procedure?
In the stored procedure, how does one change the execution process based on the query results of dynamic SQL statements?
A stored procedure a uses the name of another stored procedure B (or an SQL statement or an uncertain table name or field name) as a parameter. How can I keep the stored procedure B unchanged, filter/change the execution result record set of stored procedure B, and then return the filtered/changed result set to the caller of stored procedure?

the above problems all have one thing in common, that is, they all want to re-process the execution results of stored procedures (or dynamic SQL statements), but standard SQL statements can only process data tables, while a stored procedure (or dynamic SQL statements) but they cannot be processed as data tables. This greatly limits the application scope of stored procedures (or dynamic SQL statements, they can only serve as the last Processing Layer before returning the record set to the application Program . How nice it would be if we could use stored procedures (or dynamic SQL statements) like using common data tables.
my previous solution to this problem was to use openquery () or OpenDataSource (), but openquery () requires not only creating a linked server, but also unsatisfactory execution performance. OpenDataSource () requires the provision of connection strings, which is also a great headache for later maintenance of the system.
today, when I used SQL Server books online, I accidentally found an SQL statement, it was very convenient to solve this problem. This statement is an insert statement.
the definition of the insert statement in help is as follows:

insert [into]
{table_name with ( [... n])
| view_name
| rowset_function_limited
}

{[(Column_list)]
{Values
({Default | null | expression} [,... n])
| Derived_table
| Execute_statement
}
}
| Default values

The description of execute_statement is "any valid execute statement, which uses the select or readtext statement to return data. ". We usually place a SELECT statement in this position. But since "any valid execute statement", "Exec procedure_name" should be okay? With this in mind, I decided to verify it immediately. The verification result confirms that there is no problem. That is, the following statement

Insert into table_name exec procedure_name @ parameters_var

It does work normally. With this foundation, we also have a solution to the problems at the beginning of this article.

The basic idea is to create a temporary table first, using insert... exec... statement to save the returned results of the stored procedure to the temporary table. Then, you can treat the temporary table as you would when processing a common data table. For dynamic SQL statements, it can be executed through the DBO. sp_executesql stored procedure or directly as the exec parameter. For specific writing requirements, refer to SQL Server books online. The structure of the temporary table is compatible with the table structure of the record set returned by the stored procedure (or dynamic SQL statement. If you execute a dynamic SQL statement directly through exec, the SQL statement has a length limit of 4 K.

Finally, two common processing procedures are provided:
1. Create a temporary table # TMP. The table structure is compatible with the returned result set of the target Stored Procedure procedure_name (compatible, not the same ).
Create Table # TMP (
[Columns_list]
)
2. Execute the stored procedure and insert the returned result set of the stored procedure into the temporary table.
Insert into # TMP exec procedure_name @ parameters_var
3. Now you can use (filter, change or search) # TMP. Pai_^
If exists (select * from # TMP)
Begin
-- Execution Branch 1
End else begin
-- Execution Branch 2
End
4. Do not forget to clear the temporary table.
Drop table # TMP

For dynamic SQL statements, you only need to change the second step
Insert into # TMP exec DBO. sp_executesql @ querystring_var
You can.