CODEWITHSUNDEEP
sql-serverstored-procedures
MaazKhan47
MaazKhan47

Reputation: 849

Storing output of a stored procedure into a variable when the stored procedure is called with in another stored procedure

I am executing a stored procedure within another stored procedure and then want to store the result of the inner stored procedure into a variable to use it later. The result of the inner stored procedure will be a varchar value. How can I set a variable to the result of a stored procedure ?

ALTER PROCEDURE [dbo].[mvc_Formulas]    
    @Fund_ID nvarchar(max),
    @Start_Dated datetime,
    @End_Dated datetime
AS
    DECLARE @FormulaType int
    DECLARE @XFund_ID bigint

    DECLARE @ReturningTable TABLE(fundname varchar(100), zRETURN nvarchar(100))
    DECLARE @zRETURN nvarchar(100)
    DECLARE @zSD nvarchar(100)
    DECLARE @FUNDS TABLE(FundId BIGINT) 

    INSERT INTO @FUNDS
        SELECT item
        FROM dbo.SplitString(@Fund_ID, ',')

    DECLARE @MyCursor CURSOR;
    DECLARE @CurFund BIGINT;

    DECLARE @FundName NVARCHAR(100);
BEGIN
    SET @MyCursor = CURSOR FOR  
                         SELECT FundId FROM @FUNDS   

    OPEN @MyCursor 

    FETCH NEXT FROM @MyCursor INTO @CurFund

    WHILE @@FETCH_STATUS = 0
    BEGIN   
        SET @FundName = (SELECT FundName FROM FUNDS WHERE Fund_ID = @CurFund)

        --inner SP--------------------------------------
        SET @zRETURN = EXEC [dbo].[Fund_Performance_Graph] @Start_Dated, @End_Dated, @CurFund
        -----------------------------------------------------

        INSERT INTO @ReturningTable
            SELECT @FundName, @zReturn

        FETCH NEXT FROM @MyCursor INTO @CurFund 
    END

    SELECT * FROM @ReturningTable
END

Upvotes: 1

Views: 51

Answers (1)

Anton
Anton

Reputation: 2882

ALTER PROCEDURE [dbo].[mvc_Formulas]    
    @Fund_ID nvarchar(max),
    @Start_Dated datetime,
    @End_Dated datetime,
    @Result varchar(1000) OUTPUT

Upvotes: 1

Related Questions