How do I retrieve the result set of a stored procedure (store Procedure) in a SQL Server query statement (Select)? (2006-12-14 09:25:36)
Other descriptions of the same nature as this issue include:
How do I get an execution result recordset for another stored procedure in a SQL Server stored procedure?
How do I retrieve the execution results of a dynamic SQL statement in a stored procedure?
How to implement similar select * FROM (EXEC procedure_name @parameters_var) as DataSource ... The function? Procedure_name is the name of a stored procedure, @parameters_var a list of process parameters
How do I pass a stored procedure's execution result recordset to another stored procedure?
How does a stored procedure choose the execution process based on the execution results of another stored procedure?
How does a stored procedure change the execution process based on the query results of a dynamic SQL statement?
A stored procedure a uses the name of another stored procedure B (or a SQL statement or an indeterminate table name, field name) as an argument, how to filter/change the execution result recordset of stored procedure B without altering the stored procedure B, and then return the filtered/changed result set to the caller of stored procedure a?
All of these questions have one thing in common, which is that you want to re-process the execution of a stored procedure (or dynamic SQL statement), but a standard SQL statement can handle only the data table, while a stored procedure (or dynamic SQL statement) executes the recordset, but they cannot be treated as a data table by themselves. This greatly limits the scope of application of stored procedures (or dynamic SQL statements), which can only be used as the last processing layer before returning the recordset to the application. It would be nice if we could use a stored procedure (or dynamic SQL statement) as if we were using a normal data table.
My previous solution to this problem was to use OPENQUERY () or OpenDataSource (), but OPENQUERY () required not only to establish a linked server, but also to perform performance that was not satisfactory. OpenDataSource () requires a connection string, which is also a big hassle for later maintenance of the system.
When I use SQL Server Books Online today, I accidentally discovered an SQL statement, which is a very convenient solution to this problem. This statement is an INSERT statement.
The INSERT statement is defined in Help as follows: Insert [into]
{table_name with (< table_hint_limited > [... n])
| View_name
| rowset_function_limited
} {[(column_list)]
{VALUES
({DEFAULT | NULL | expression} [,... n])
| Derived_table
| Execute_statement
}
}
| DEFAULT values where execute_statement is interpreted as "any valid EXECUTE statement that returns data using a SELECT or READTEXT statement." "。 Usually we put this position in a SELECT statement. But to help say "any valid EXECUTE statement", then "EXEC procedure_name" should also be able to luo? Thinking about it, I decided to try 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_valuedoes work correctly. With this foundation, we have a way to solve the problems at the beginning of this article. The basic idea is to first create a temporary table, through the insert ... Exec... Statement to save the returned results of a stored procedure to a temporary table, you can then treat the temporary table as you would with a normal data table. For dynamic SQL statements, you can execute them either through the Dbo.sp_executesql stored procedure or directly as an exec parameter. You can refer to SQL Server Books Online for specific authoring requirements. In particular, the table structure of the temporary table is compatible with the table structure of the recordset returned by the stored procedure (or dynamic SQL statement) and is not required to be identical. If you execute a dynamic SQL statement directly from EXEC, the SQL statement has a length limit of 4 K.
Finally, two of the most common processing processes are given:
1. Create a temporary table #tmp, the table structure is compatible with the return result set procedure_name the target stored procedure (compatible, not necessarily the same).
CREATE TABLE #tmp (
[Columns_list]
)
2. Executes the stored procedure and inserts the returned result set of the stored procedure into the staging table.
INSERT into #tmp EXEC procedure_name @parameters_var
3. You can now use (filter, change, or retrieve) #tmp了. ^_^
IF EXISTS (SELECT * from #tmp)
BEGIN
--Execution Branch 1
END ELSE BEGIN
--Execution Branch 2
END or SELECT * from #tmp WHERE ...
4. Don't forget to finally clear the temp table.
DROP TABLE #tmp for dynamic SQL statements, just change the second step to
INSERT into #tmp EXEC dbo.sp_executesql @querystring_value
Can.
How do I retrieve the result set of a stored procedure (store Procedure) in a SQL Server query statement (Select)?
