GenesysStack.com

Did You Know that you can capture results from a Stored Procedure into a Table variable?

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.

Leave your comment
Comments
12/2/2016 12:37 AM
Thanks for the tip, never had thought about doing this before.