Combine Two Storage Procudure...

First Parent procedure return One table .From That table we extract One column values based on every record generate another table
Actually I need Multiple result set based on SP1 Return  values AthleteID SP2

ALTER PROCEDURE sp1

AS
BEGIN
Select AthleteID  from table1 
end

ALTER PROCEDURE sp2
@AthleteID int
AS
BEGIN
Select * from table2 where AthleteID =@AthleteID 
end

ALTER PROCEDURE [Following].[SearchFollowingItems]
	@UserID INT,
	@Keyword VARCHAR(500)=null,
	@Prefix VARCHAR=NULL,
	@IsFeatured bit=null,
	--@Discipline INT=null,
	--@Entity INT=null,
	@IsFollowed BIT=null,
	@PageSize INT=0,
	@PageIndex INT=0,
	
	@PopularOnly BIT=null,
	@Limit bigint=9223372036854775807
	--@TotalPages INT OUT,
	--@TotalRecords INT OUT
AS
BEGIN
SET NOCOUNT ON;

--athlete

--DECLARE @Page int=@PageSize
--IF @Page=0
--SET @Page = 1

;WITH 
popular AS(SELECT FollowingItemID, count(1) Popularity from Following.UserFollowing 
where IsActive=1
GROUP BY FollowingItemID),

A AS (
SELECT --(row_number() OVER (ORDER BY SP.bsn_StandardProfile_ID ASC)) RowNumber
	   --, CEILING((row_number() OVER (ORDER BY SP.bsn_StandardProfile_ID ASC) - 1) / @Page) AS PageNumber
	   P.PerformanceProfileID AS ItemID
	 , PD.FirstName FirstName
	 , PD.LastName LastName
	 , O.OrganisationID
	 --zahir
	 --, PA.bsn_Organistation_ID
	 , UF.ID FollowingID
	 , popular.Popularity	
FROM
	--[dbo].[tf_Profile_Athlete] PA	
	--INNER JOIN 
	PerformanceProfile.Profile P
		--ON P.PerformanceProfileID = PA.bsn_StardardProfile_ID
	LEFT JOIN
		Following.UserFollowing UF
		ON UF.EntityID = 1 AND UF.IsActive = 1 AND UF.UserID = @UserID AND UF.FollowingItemID = P.PerformanceProfileID
	LEFT JOIN popular ON popular.FollowingItemID=P.PerformanceProfileID
	INNER JOIN PerformanceProfile.ProfileVersion as PV on P.PerformanceProfileID=PV.PerformanceProfileID --AND P.AttributeFlags=1
	INNER JOIN Shared.PersonalDetails as PD on PD.PersonalDetailsId=PV.PersonalDetailsID 
	INNER join PerformanceProfile.[Image] as I on I.PerformanceProfileID=P.PerformanceProfileID
	--Zahir For getting Org. Id
	join Organisation.Organisation as O on I.OrganisationID=O.OrganisationID --AND O.AttributeFlags=1
	join Organisation.OrganisationVersion as OV on OV.OrganisationID = O.OrganisationID
	
WHERE
	(@Keyword IS NULL OR @Keyword=''
	OR (SOUNDEX(PD.FirstName) = SOUNDEX(@Keyword)
	OR SOUNDEX(PD.LastName) = SOUNDEX(@Keyword)))
	AND (@IsFollowed IS NULL
	OR (UF.FollowingItemID IS NULL
	AND @IsFollowed = 0)
	OR (UF.FollowingItemID IS NOT NULL
	AND @IsFollowed = 1))
	AND (@IsFeatured IS NULL OR  PV.AttributeFlags=@IsFeatured)
	AND (@PopularOnly=0 OR (popular.Popularity IS NOT NULL AND popular.Popularity > 0))
	AND PV.IsLatest=1
	--AND (@Discipline IS NULL OR @Discipline = athlete.DisciplineID)
	--AND (@Entity IS NULL OR EntityID = @Entity
	--ORDER BY
--    case @PopularOnly
--    when 1  then 'popular.Popularity desc'
--    else  ''
--    end
			 )
,abc AS (
SELECT DISTINCT left(LastName, 1) Letter
FROM
	a
)
,
Letters
AS (
SELECT (SELECT Letter + ','
		FROM
			abc
		FOR XML
			PATH ('')) Letters
)

,
pagetable
AS (
SELECT TOP (@Limit) * 
	,ROW_NUMBER() OVER(ORDER BY 
	    case @PopularOnly
		when 1  then 'popular.Popularity desc'
		else  'LastName ASC'
		end
	) RowIndex
	 --, CEILING((row_number() OVER (ORDER BY LastName ASC) - 1) / @Page) AS PageNumber
FROM
	a
WHERE
	(@Prefix IS NULL
	OR LastName LIKE @Prefix + '%')
)

,
totalCount
AS (
SELECT count(1) Total
FROM
	pagetable
)

SELECT  
		PA.*
	 , PD.DateOfBirth DateOfBirth
	 , PD.Gender AS Gender
	 ,PV.Abstract
	 , (SELECT TOP 1 C.StartDate
		FROM
			TF.EventResults ER
			INNER JOIN Tf.PerformanceProfileResultsJoin Prj
				ON Prj.ResultsId = Er.ResultsId
			LEFT JOIN tf_Competition C
				ON C.tf_Competition_ID = ER.CompetitionID
		WHERE
			Prj.PerformanceProfileID = PA.ItemID
		ORDER BY
			C.StartDate ASC) Debut	
	 , I.ImageUrl AS ItemImageUrl
	 , OV.OrganisationID AS 'OrganisationID'
	 , OV.Name AS 'OrganisationName'
	 , OV.ShortName AS 'OrganisationShortName'
	 --, [dbo].[GetAthleteSports](SP.bsn_StandardProfile_ID) AS 'Sports'
	   , I.ImageUrl ProfilePicture
	 , Total
	 , Letters.Letters
	 , ISNULL(CPD.FirstName,'') + ISNULL(CPD.LastName,'') AS 'CoachName'
FROM 
	totalCount, Letters, pagetable PA
	INNER JOIN
		PerformanceProfile.Profile P
		ON P.PerformanceProfileID = PA.ItemID
	LEFT OUTER JOIN
		PerformanceProfile.Image AS I
		ON I.PerformanceProfileID = P.PerformanceProfileID /*AND PP.ProfilepicDisciplineID = 1	*/
	LEFT OUTER JOIN
		Organisation.OrganisationVersion OV
		ON OV.OrganisationID = PA.OrganisationID
		--Zahir
	--LEFT JOIN bsn_User Coach
	--	ON SP.bsn_StandardProfile_Linked_bsn_User_ID = Coach.bsn_User_ID
	LEFT JOIN PerformanceProfile.ProfileVersion AS PV 
		ON P.PerformanceProfileID=PV.PerformanceProfileID
	LEFT JOIN Shared.PersonalDetails AS PD 
		ON PD.PersonalDetailsId=PV.PersonalDetailsID
	LEFT JOIN PerformanceProfile.OrganisationJoin as OJ on PV.VersionID=OJ.PerformanceProfileVersionID
	LEFT JOIN PerformanceProfile.OrganisationJoin as COJ ON OJ.OrganisationID = COJ.OrganisationID AND 
	COJ.PerformanceProfileType = 4
	LEFT JOIN PerformanceProfile.ProfileVersion CPV ON COJ.PerformanceProfileVersionID = CPV.VersionID
	LEFT JOIN Shared.PersonalDetails CPD ON CPD.PersonalDetailsId = CPV.PersonalDetailsID
	--inner Join Security.BrawtaEntity as BE on OJ.PerformanceProfileType=BE.EntityId --AND BE.EntityName='Coach'
	--inner Join Shared.PersonalDetails as SPD on PD.PersonalDetailsId = SPD.PersonalDetailsId
WHERE
	@PageSize = 0 OR (PA.RowIndex BETWEEN (@PageIndex * @PageSize + 1)
        AND ((@PageIndex +1 )* @PageSize)) --PA.PageNumber = @PageIndex

	
END



SP2





ALTER PROCEDURE [Following].[GetAthleteRecord]
	@AthleteID int,
	@AttributeFlags tinyint=NUll
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

DECLARE @LatestSeason int

SELECT @LatestSeason = S.SeasonID
FROM
	Competition.Season S
	INNER JOIN
		(SELECT max(StartDate) S
		 FROM
			 Competition.Season) S2
		ON S.StartDate = S2.S


;WITH Record as(
SELECT 
	E.ShortCode
	 , min(ER.ResultsHEPDEC) Record
FROM
	TF.EventResults ER
	LEFT JOIN TF.[Events]  E
		ON E.ShortCode = ER.Event
	LEFT JOIN Competition.Competition C
		ON C.CompetitionID = ER.CompetitionID 
   LEFT JOIN TF.PerformanceProfileResultsJoin PPR ON ER.ResultsId=PPR.ResultsId
   LEFT JOIN Competition.CompetitionVersion CC ON C.CompetitionID=CC.CompetitionID
WHERE 
	PPR.PerformanceProfileID = @AthleteID
	AND C.SeasonID = @LatestSeason
	AND (@AttributeFlags IS NULL OR CC.AttributeFlags = @AttributeFlags)
	AND ER.PositionInRace != 0
GROUP BY
	E.ShortCode)

SELECT DISTINCT R.Record
	 , R.ShortCode AS 'MMCode'
	 , E.Name EventName
	 , CB.ShortName AS 'CompetitionShortName'
	 , CC.AttributeFlags
FROM
	Record R
	LEFT JOIN TF.EventResults ER ON ER.Event=R.ShortCode AND ER.ResultsHEPDEC=R.Record
	LEFT JOIN Competition.Competition C ON ER.CompetitionID=C.CompetitionID
	LEFT JOIN Competition.CompetitionVersion CC ON C.CompetitionID=CC.CompetitionID
	LEFT JOIN Competition.BaseCompetition CB ON C.BaseCompetitionID=CB.BaseCompetitionID
	LEFT JOIN TF.[Events] E
		ON E.ShortCode = R.ShortCode
		
END
<pre>