locked
How to implement a SQL stored procedure based on anotherstored procedure information? RRS feed

  • Question

  • User-609535877 posted

    I have a SQL stored procedure to filter our company's employee overtime data such as employeeID, GroupID, dateOverTime,, OverTimeHours, OvetTimeDollar, OvertimeReasonID implemented by third party.

    Now I am required to implement a summary report on the information provider by the first stored procedure to get such as total OverTimeHourm Total Dollar for each OvertimeReasonID for a time period.

    What is best way to implement a SQL stored procedure for my case? Thanks in advance!

    Tuesday, September 10, 2019 3:50 PM

Answers

  • User2053451246 posted

    Declare a temp table or table variable.  Insert the results of the SP into it.  Work with the temp table or table variable to produce your results.  This should explain enough to get you started.

    https://dba.stackexchange.com/a/132510

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, September 10, 2019 7:37 PM
  • User-719153870 posted

    Hi zhao790,

    According to your description, you want to use your first stored procedure's result( overtime related information) to be your second stored procedure's parameter?

    If so, you might need to check Pass One Stored Procedure’s Result as Another Stored Procedure’s Parameter.

    -- Pass One Stored Procedure's Result as Another Stored Procedure's Parameter
    DECLARE @ParamtoPass INT, @CircleArea FLOAT
    -- First SP
    EXEC @ParamtoPass = SquareSP 5
    -- Second SP
    EXEC @CircleArea = FindArea @ParamtoPass
    SELECT @CircleArea FinalArea
    GO

    In this case, you will just need to EXEC the first SP then EXEC the second one with its result as the first SP's parameter.

    If i misunderstood anything, please feel free to let me know.

    Best Regard,

    Yang Shen

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, September 11, 2019 5:19 AM

All replies

  • User2053451246 posted

    Declare a temp table or table variable.  Insert the results of the SP into it.  Work with the temp table or table variable to produce your results.  This should explain enough to get you started.

    https://dba.stackexchange.com/a/132510

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, September 10, 2019 7:37 PM
  • User-719153870 posted

    Hi zhao790,

    According to your description, you want to use your first stored procedure's result( overtime related information) to be your second stored procedure's parameter?

    If so, you might need to check Pass One Stored Procedure’s Result as Another Stored Procedure’s Parameter.

    -- Pass One Stored Procedure's Result as Another Stored Procedure's Parameter
    DECLARE @ParamtoPass INT, @CircleArea FLOAT
    -- First SP
    EXEC @ParamtoPass = SquareSP 5
    -- Second SP
    EXEC @CircleArea = FindArea @ParamtoPass
    SELECT @CircleArea FinalArea
    GO

    In this case, you will just need to EXEC the first SP then EXEC the second one with its result as the first SP's parameter.

    If i misunderstood anything, please feel free to let me know.

    Best Regard,

    Yang Shen

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, September 11, 2019 5:19 AM