Sharing: Method of using a query result set returned by another stored procedure in a stored procedure _mssql

Many of the query class's stored procedures return a result set of a table structure, and if you need to use this result set in other stored procedures, you can use the former query results directly to avoid writing duplicate SQL scripts.

For example, a stored procedure Sp_getborrowrecord @BeginTime, @EndTime can query all library records within a certain time range (start time @begintime, end time @endtime). This stored procedure can be used for the query page of the library record.

Now you need to implement another feature: summarize and analyze the library records for a certain period of time. If grouped by the type of books borrowed, and sorted by the total number of books in each category. You can, of course, directly requery by using the methods of group by, but for this example, the implementation is done in a different way, and the method has its own advantages (mentioned at the end of the article). Build a new stored procedure as follows:

Copy Code code as follows:

--access to library records analysis
Create proc Sp_getborrowanalysis
@BeginTime datetime,
@EndTime datetime
As
--Build a table variable with the same structure as the result set Sp_getborrowrecord query
DECLARE @Record table
(
BookID int,--book ID
BookName varchar (100),--book name
typeID int,--Book category ID
Cardid INT--Library card ID
Cardname varchar (100)--the name of the borrower
)

--Get the library record during this time and deposit it in the @record table variable
INSERT INTO @Record
exec Sp_getborrowrecord @BeginTime, @EndTime

-Next, you can perform the required processing of the detected data (the data in @Record) as you would with a common table
--。。。。。
Go

The advantage of using this approach is that you can avoid writing duplicate code and facilitate maintenance. If the library records of the query conditions change, for example, in addition to screening according to the time range, but also according to the library card is valid, or whether the books are still in existence and other conditions to filter, then only need to maintain sp_getborrowrecord stored procedures, There is no need to modify all the stored procedures involved in querying a library record. But this method in the case of large amount of data to compare the impact of performance, query speed is slow, in the case of small amount of data this difference is not obvious.

Note: This method is not supported by a lower version of SQL Server, which needs to be used in SQL Server2005 or later.