CODEWITHSUNDEEP
sql-serverstored-procedures
user6565467
user6565467

Reputation: 49

Get value of a particular column from a SQL stored procedure which returns table

I have a stored procedure lets say its name is SP1, and it returns a table. Now I have another stored procedure SP2.

What I am want to do something like this inside SP2:

set @SP2_var = SP1.column_name

so that the @SP2_var can access the value. The table returned by SP1 is a temporary table like Select * from #temp. 

ALTER PROCEDURE [dbo].[GetConditionDataByRouteId]
    @RouteId INT
AS
    DECLARE @obj sys.Geography
    DECLARE @reportedDate DateTime
    Select @obj= CachedRoute From Route where Id=@RouteId
    SELECT * INTO #temp FROM (SELECT * FROM PavementRawDataTable Where BeginGeo.STDistance(@obj)<=5 and AgencyId=dbo.GetAgencyId(@RouteId)) as X
    SELECT Top 1 @reportedDate= SurveyDateTime FROM [#temp] 
    SELECT 
      Avg([RQI]) RQI,
      (SELECT RQIOver FROM PF_Condition WHERE SegmentId=@RouteId) RQIOver,
      Avg([SR]) SR,
      (SELECT SROver FROM PF_Condition WHERE SegmentId=@RouteId) SROver,
      Avg([PQI]) PQI,
      (SELECT PQIOver FROM PF_Condition WHERE SegmentId=@RouteId) PQIOver,
      Avg([IRIAvg]) IRI,
      (SELECT IRIOver FROM PF_Condition WHERE SegmentId=@RouteId) IRIOver,
      NULL PCI,
      (SELECT PCIOver FROM PF_Condition WHERE SegmentId=@RouteId) PCIOver,
      Avg([RutAvg]) Rut,
      (SELECT RUTOver FROM PF_Condition WHERE SegmentId=@RouteId) RUTOver,
      dbo.GetPlannedProjectYear(@RouteId) PlannedProjYear ,
      (SELECT PlannedProjectYearOver FROM PF_Condition WHERE SegmentId=@RouteId) PlannedProjectYearOver,
      dbo.GetPlannedProjectType(@RouteId) PlannedProjType,
      (SELECT PlannedProjectTypeOver FROM PF_Condition WHERE SegmentId=@RouteId) PlannedProjectTypeOver,
      @reportedDate CollectedDate,
      (SELECT DateCollectedOver FROM PF_Condition WHERE SegmentId=@RouteId) DateCollectedOver

  FROM [#temp]  
 END

This is SP1. And I want to store the value PQI from this stored procedure to a variable in SP2.

Any help is really appreciated. Thanks

Upvotes: 1

Views: 1286

Answers (1)

Jason W
Jason W

Reputation: 13209

Here is an example of the INSERT/EXEC approach applied to your queries.

-- You'll need to define the data types being returned in the result set
DECLARE @Results TABLE (
    RQI DECIMAL,
    RQIOver DECIMAL,
    SR DECIMAL,
    SROver DECIMAL,
    PQI DECIMAL,
    PQIOver DECIMAL,
    IRI DECIMAL,
    IRIOver DECIMAL,
    PCI DECIMAL,
    PCIOver DECIMAL,
    Rut DECIMAL,
    RUTOver DECIMAL,
    PlannedProjYear DECIMAL,
    PlannedProjectYearOver DECIMAL,
    PlannedProjectTypeOver DECIMAL,
    CollectedDate DATETIME,
    DateCollectedOver DATETIME
)
-- Call SP (assumes you already have @RouteId defined)
INSERT @Results
    EXEC [dbo].[GetConditionDataByRouteId] @RouteId

-- Now you can refer to SP1 column from result set
SET @SP2_var = (SELECT TOP 1 RQI FROM @Results)

UPDATE

If you wanted to share the temp table, then it would have to be created in the calling stored procedure. For example:

CREATE PROC SP1
AS BEGIN
    SET NOCOUNT ON
    UPDATE #temp SET A = 2
END
GO

CREATE PROC SP2
AS BEGIN
    SET NOCOUNT ON
    SELECT 1 AS A INTO #temp
    EXEC SP1
    SELECT * FROM #temp -- This is now "2"
END
GO

Here SP2 stored proc creates the temp table, so when it calls SP1, then SP1 can see and update it.

This can be risky since SP1 has to assume the caller has created the temp table it is expecting to exist. INSERT/EXEC may be a cleaner and safer implementation for many cases.

Upvotes: 1

Related Questions