Use values from stored procedure in another stored procedure

Question

So, in the project I'm working on, there is a stored procedure (which we will call SP_Premium) that gets me a few values, including a Premium number, which I need.

I've figured out that I can't use Linq to EF to solve my problem, so I should use a stored procedure.

The problem here is, I need the Premium number from SP_Premium inside my new stored procedure.

How do I add this SP_Premium to my own stored procedure and get all the values out?

ALTER PROCEDURE [dbo].[SP_Premium]
 @RequestId int = 104622
AS

BEGIN
declare @precision int = 10

select 
     P.Premium
     P.ONP
    ,P.ONEP
    ,P.retention
    ,P.retentionValue
    ,P.retentionValueEndorsement
    ,P.OCP
    ,P.OCEP
    ,P.cededShare
    ,T.amount
    ,T.endorsementAmount
    ,T.cededAmount
    ,T.cededEndorsementAmount
    ,RC.cost
    ,RC.endorsementCost
 from requestbackoffice bo
    cross apply [dbo].[Report_Get_BO_original_premium](@RequestId,@precision) P
    outer apply [dbo].[Report_Get_BO_taxes](@RequestId,@precision) T
    outer apply [dbo].[Report_Get_BO_reinsurance_costs](@RequestId,@precision) RC
 where bo.requestId=@requestId
END

Have you looked at [Insert results of a stored procedure into a temporary table](https://stackoverflow.com/questions/653714/insert-results-of-a-stored-procedure-into-a-temporary-table?rq=1) shown under **Related** on the right side of the page? — HABO, Nov 27 '18 at 12:11
I did now, but as I'm not an expert on stored procedures, I'm not sure if I need to store my data in a temp table. — Jordec, Nov 27 '18 at 12:13
Because in the whole project this is only used once, so I feel anxious about this technique. — Jordec, Nov 27 '18 at 12:28

score 1 · Answer 1 · answered Nov 27 '18 at 14:26

Within the new stored procedure, you can just execute the existing procedure and save the results there for use in that new code.

If it was me, I'd start by just executing the existing query from SP_Premium (Note: Avoid Naming User Stored Procedures SP% or SP_%), using an INTO clause to create a table, then use management studio to script out the resulting table. Use that script to generate a temporary table in your new procedure. It's the lazy way of getting all the data types right.

So, pseudo-coding would look something like this:

CREATE PROCEDURE usp_NewProc
AS

BEGIN

  IF OBJECT_ID('tempdb..#InterimTable','U') IS NOT NULL
    DROP TABLE #InterimTable;

  CREATE TABLE #InterimTable(
  <using the script that SSMS made for you>
  );

  DECLARE @ParamVariable INT;

  SELECT @ParamVariable = <Code to assign a parameter value>;

  INSERT INTO #InterimTable (<Field List>)
  EXECUTE SP_Premium @RequestID = @ParamVariable;

  <Carry on with whatever else you need to do>;

  --Arguably unnecessary, but I like to clean up after myself
  IF OBJECT_ID('tempdb..#InterimTable','U') IS NOT NULL
    DROP TABLE #InterimTable;

END;

score 0 · Answer 2 · answered Nov 27 '18 at 12:34

I think that the best way is to use temp table and then drop it like:

ALTER PROCEDURE [dbo].[SP_Premium]
 @RequestId int = 104622
AS

BEGIN
declare @precision int = 10

IF object_id('tempdb..##tmp_result_sp_premium') IS NOT NULL 
    DROP TABLE ##tmp_result_sp_premium

select 
     P.Premium
     P.ONP
    ,P.ONEP
    ,P.retention
    ,P.retentionValue
    ,P.retentionValueEndorsement
    ,P.OCP
    ,P.OCEP
    ,P.cededShare
    ,T.amount
    ,T.endorsementAmount
    ,T.cededAmount
    ,T.cededEndorsementAmount
    ,RC.cost
    ,RC.endorsementCost
 into ##tmp_result_sp_premium
 from requestbackoffice bo
    cross apply [dbo].[Report_Get_BO_original_premium](@RequestId,@precision) P
    outer apply [dbo].[Report_Get_BO_taxes](@RequestId,@precision) T
    outer apply [dbo].[Report_Get_BO_reinsurance_costs](@RequestId,@precision) RC
 where bo.requestId=@requestId
END

then in your procedure use the results and then

IF object_id('tempdb..##tmp_result_sp_premium') IS NOT NULL 
    DROP TABLE ##tmp_result_sp_premium

Using ## instead # allow you to access data in other sessions.

Use values from stored procedure in another stored procedure

2 Answers2