结合两个存储过程...... [英] Combine Two Storage Procudure...
本文介绍了结合两个存储过程......的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
第一个父程序返回一个表。从那个表我们提取基于每个记录的一个列值生成另一个表
实际上我需要基于SP1的多个结果集返回值AthleteID SP2
ALTER PROCEDURE sp1
AS
BEGIN
选择 AthleteID 来自 table1
end
ALTER PROCEDURE sp2
@AthleteID int
AS
BEGIN
从 table2中选择 * 其中 Athl eteID = @ AthleteID
end
相同作为SP1
ALTER PROCEDURE [关注]。[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 ;
- 运动员
- DECLARE @Page int = @ PageSize
- IF @ Page = 0
- SET @Page = 1
; WITH
热门 AS （ SELECT FollowingItemID，count（ 1 ）人气来自关注。用户关注
其中 IsActive = 1
GROUP BY FollowingItemID），
A AS （
< span class =code-keyword> SELECT - （row_number（）OVER（按SP排序）。 BS n_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 FollowingIDID b $ b，popular.Popularity
FROM
- [dbo]。[tf_Profile_Athlete] PA
- INNER JOIN
PerformanceProfile.Profile P
- ON P.PerformanceProfileID = P A.bsn_StardardProfile_ID
LEFT JOIN
关注.UserFollowing UF
ON UF.EntityID = 1 AND UF.IsActive = 1 AND UF.UserID = @UserID AND UF.FollowingItemID = P.PerformanceProfileID
LEFT JOIN 热门 ON popular.FollowingItemID = P.PerformanceProfileID
INNER JOIN PerformanceProfile.ProfileVersion as PV on P.PerformanceProfileID = PV.PerformanceProfileID - AND P.Attribu teFlags = 1
INNER JOIN Shared.PersonalDetails as PD PD.PersonalDetailsId = PV.PersonalDetailsID
INNER join PerformanceProfile。[图像] 我 I.PerformanceProfileID = P.PerformanceProfileID
- Zahir获得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
（ @关键字 IS NULL 或 @ Keyword = ' '
OR （SOUNDEX（PD.FirstName）= SOUNDEX（ @ Keyword ）
或 SOUNDEX（ PD.LastName）= SOUNDEX（ @关键字）））
AND （ @ IsFollowed IS NULL
OR （UF.FollowingItemID IS NULL
AND @ IsFollowed = 0 ）
OR （UF .FlowinglowingItemID IS NOT NULL
AND @ IsFollowed = 1 ））
AND （ @ IsFeatured IS NULL 或 PV.AttributeFlags=@IsFeatur ed）
AND （@ PopularOnly = 0 OR （popular.Popularity IS NOT NULL AND popular.Popularity> 0 ））
AND PV.IsLatest = 1
- AND（@Discipline IS NULL或@Discipline = athlete.DisciplineID）
- AND（@Entity IS NULL或EntityID = @Entity
- ORDER BY
- case @PopularOnly
- 1当时'popular.Popularity desc'
- else''
- end
）
，abc AS （
SELECT DISTINCT left （LastName， 1 ）字母
FROM
a
）
，
字母
AS （
SELECT （ SELECT Letter + ' ，'
FROM
abc
FOR XML
PATH （' '））来信
）
，
pagetable
AS （
SELECT TOP （ @ Limit ）*
，ROW_NUMBER（） OVER （ ORDER BY
case < span class =code-sdkkeyword> @ PopularOnly
当 1 然后 ' 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
或 LastName LIKE @ Prefix + ' ％'）
）
，
totalCount
AS （
SELECT count（ 1 ）总计
FROM
pagetable
）
SELECT
PA。*
，PD.DateOfBirth DateOfBirth
，PD.Gender < span class =code-keyword> AS 性别
，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 = E R.CompetitionID
WHERE
Prj.PerformanceProfileID = PA.ItemID
ORDER BY
C.StartDate ASC ）首次亮相
，I.ImageUrl < span class =code-keyword> AS ItemImageUrl
，OV.OrganisationID AS ' OrganisationID'
，OV.Name AS ' OrganisationName'
，OV.ShortName AS OrganisationShortName'
- ，[dbo]。[GetAthleteSports]（SP.bsn_StandardProfile_ID）AS'Floys'
，I.ImageUrl ProfilePicture
，
总计，字母。字母
，ISNULL（C PD.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。图片 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 共享。 PersonalDetails CPD ON CPD.PersonalDetailsId = CPV.PersonalDetailsID
- inner将Security.BrawtaEntity加入为OJ.PerformanceProfileType上的BE = BE.EntityId --AND BE.EntityName ='Coach'
- inner Join Shared.PersonalDetails作为PD上的SPD.PersonalDetailsId = SPD.PersonalDetailsId
WHERE
@PageSize = 0 OR （PA.RowIndex BETWEEN （ @ PageIndex * @ PageSize + 1 ）
AND （（ @ PageIndex +1）* @ PageSize ）） - PA.PageNumber = @PageI ndex
END
SP2
ALTER PROCEDURE [关注]。[GetAthleteRecord]
@ AthleteID int ，
@ AttributeFlags tinyint = NUll
AS
< span class =code-keyword> BEGIN
- 添加SET NOCOUNT ON以防止额外的结果集来自
- 干扰SELECT语句。
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 记录为（
SELECT
E.ShortCode
，min（ER.ResultsHEPDEC）记录
FROM
TF.EventResults ER
LEFT JOIN TF。[活动] 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 < span class =code-keyword> 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
记录R
LEFT JOIN TF.EventResults ER ON ER.Event = R.Sho rtCode 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>
ItemId基于AuthelicID
解决方案
< blockquote>尝试：
SELECT * FROM table2 t2 JOIN table1 t1 ON t1.AthleteID = t2.AthleteID
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
Same as SP1
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>
ItemId based On AuthelicID
解决方案
Try:
SELECT * FROM table2 t2 JOIN table1 t1 ON t1.AthleteID=t2.AthleteID
这篇关于结合两个存储过程......的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
