CODEWITHSUNDEEP
t-sqlstored-procedures
Jordec
Jordec

Reputation: 1564

Use values from stored procedure in another stored procedure

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

Upvotes: 0

Views: 60

Answers (2)

Eric Brandt
Eric Brandt

Reputation: 8101

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;

Upvotes: 1

user10679624
user10679624

Reputation:

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.

Upvotes: 0

Related Questions