Did You Know that you can capture results from a Stored Procedure into a Table variable?
Stored Procedures are sometimes a black box. Maybe the SP is bloated…large, complicated, un-maintainable. Maybe the SP is a central object and can’t be easily changed.
Did you know that you can capture the results of a stored procedure, for further querying, filtering and manipulation?
Use the following technique to capture the results of a stored procedure into a table variable. This example saves a normalized TimeRange record (begin Datetime2, end Datetime2) and saves the TimeRangeID into an Appointment table:
-- Save Time Range in central stored procedure
DECLARE @Result TABLE (ID INT)
InsertInto @Result Exec [Entity].[TimeRangeSave]@BeginDate, @EndDate, @ActivityID
Select @TimeRangeID = ID From @Result
The example calls a stored procedure that returns the primary key ID of a table, and saves that ID as a foreign-key.