StmtText
GetTeamLineup @FixtureId = 318876, @UserId = 36, @TeamId = 11
CREATE PROCEDURE [dbo].[GetTeamLineup]
@UserId INT,
@FixtureId,
@TeamId
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
SELECT mt.Surname,
COALESCE( ulr.lr_Rating, tlr.lr_Rating, mt.MatchRating ),
COUNT( sc.PlayRef ) AS GoalsScored,
SUM(
CASE bk.BookingType
WHEN 'Y' THEN 1
ELSE 0
END
) AS YellowCards,
SUM(
CASE bk.BookingType
WHEN 'YR' THEN 1
WHEN 'R' THEN 1
ELSE 0
END
) AS RedCards,
mt.RefNo
FROM MATCHmagTeams mt
INNER JOIN TeamSquads ts ON ts.RefNo = mt.RefNo
INNER JOIN Teams te ON te.TeamId = mt.TeamId
LEFT JOIN BlockLineups ubl
ON ubl.bli_FixtureId = @FixtureId AND ubl.bli_UserId = @UserId
LEFT JOIN LineupRatings ulr
ON ulr.lr_BlockLineupId = ubl.bli_UniqId AND ulr.lr_PlayerId = mt.RefNo
LEFT JOIN BlockLineups tbl
ON tbl.bli_FixtureId = @FixtureId AND tbl.bli_UserId = dbo.GetUserTemplateUserId( @UserId )
LEFT JOIN LineupRatings tlr
ON tlr.lr_BlockLineupId = tbl.bli_UniqId AND tlr.lr_PlayerId = mt.RefNo
LEFT JOIN Scorers sc ON sc.PlayRef = mt.RefNo AND sc.FixId = MatchIdNo
LEFT JOIN Bookings bk ON bk.PlayRef = mt.RefNo AND bk.FixId = MatchIdNo
WHERE MatchIdNo = @FixtureId
AND te.TeamId = @TeamId
GROUP BY mt.Surname, COALESCE( ulr.lr_Rating, tlr.lr_Rating, mt.MatchRating ), mt.RefNo
ORDER BY mt.Surname
(3 row(s) affected)
StmtText
|--Compute Scalar(DEFINE:([Expr1025]=CONVERT_IMPLICIT(int,[Expr1038],0)))
|--Stream Aggregate(GROUP BY:([PDMSoccerSQL2000].[dbo].[MATCHmagTeams].[Surname], [Expr1024], [PDMSoccerSQL2000].[dbo].[MATCHmagTeams].[RefNo]) DEFINE:([Expr1038]=COUNT([PDMSoccerSQL2000].[dbo].[Scorers].[PlayRef]), [Expr1026]=SUM([Expr1030]), [Expr1027]=SUM([Expr1031])))
|--Sort(ORDER BY:([PDMSoccerSQL2000].[dbo].[MATCHmagTeams].[Surname] ASC, [Expr1024] ASC, [PDMSoccerSQL2000].[dbo].[MATCHmagTeams].[RefNo] ASC))
|--Compute Scalar(DEFINE:([Expr1024]=CASE WHEN [MyFootballYear].[dbo].[LineupRatings].[lr_Rating] as [ulr].[lr_Rating] IS NOT NULL THEN CONVERT_IMPLICIT(real(24),[MyFootballYear].[dbo].[LineupRatings].[lr_Rating] as [ulr].[lr_Rating],0) ELSE CASE WHEN [MyFootballYear].[dbo].[LineupRatings].[lr_Rating] as [tlr].[lr_Rating] IS NOT NULL THEN CONVERT_IMPLICIT(real(24),[MyFootballYear].[dbo].[LineupRatings].[lr_Rating] as [tlr].[lr_Rating],0) ELSE [PDMSoccerSQL2000].[dbo].[MATCHmagTeams].[MATCHrating] END END, [Expr1030]=CASE WHEN [PDMSoccerSQL2000].[dbo].[Bookings].[BookingType]='Y' THEN (1) ELSE (0) END, [Expr1031]=CASE WHEN [PDMSoccerSQL2000].[dbo].[Bookings].[BookingType]='YR' THEN (1) ELSE CASE WHEN [PDMSoccerSQL2000].[dbo].[Bookings].[BookingType]='R' THEN (1) ELSE (0) END END))
|--Hash Match(Right Outer Join, HASH:([PDMSoccerSQL2000].[dbo].[Bookings].[PlayRef])=([PDMSoccerSQL2000].[dbo].[MATCHmagTeams].[RefNo]), RESIDUAL:([PDMSoccerSQL2000].[dbo].[Bookings].[PlayRef]=[PDMSoccerSQL2000].[dbo].[MATCHmagTeams].[RefNo]))
|--Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1021]))
| |--Index Seek(OBJECT:([PDMSoccerSQL2000].[dbo].[Bookings].[_dta_index_Bookings_15_1525580473__K6_K1_3_4_5]), SEEK:([PDMSoccerSQL2000].[dbo].[Bookings].[FixID]=[@FixtureId]) ORDERED FORWARD)
| |--RID Lookup(OBJECT:([PDMSoccerSQL2000].[dbo].[Bookings]), SEEK:([Bmk1021]=[Bmk1021]) LOOKUP ORDERED FORWARD)
|--Hash Match(Right Outer Join, HASH:([PDMSoccerSQL2000].[dbo].[Scorers].[PlayRef])=([PDMSoccerSQL2000].[dbo].[MATCHmagTeams].[RefNo]), RESIDUAL:([PDMSoccerSQL2000].[dbo].[Scorers].[PlayRef]=[PDMSoccerSQL2000].[dbo].[MATCHmagTeams].[RefNo]))
|--Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1018]))
| |--Index Seek(OBJECT:([PDMSoccerSQL2000].[dbo].[Scorers].[IX_Scorers]), SEEK:([PDMSoccerSQL2000].[dbo].[Scorers].[FixID]=[@FixtureId]) ORDERED FORWARD)
| |--RID Lookup(OBJECT:([PDMSoccerSQL2000].[dbo].[Scorers]), SEEK:([Bmk1018]=[Bmk1018]) LOOKUP ORDERED FORWARD)
|--Nested Loops(Left Outer Join, OUTER REFERENCES:([PDMSoccerSQL2000].[dbo].[MATCHmagTeams].[RefNo], [tbl].[bli_UniqId], [Expr1037]) WITH UNORDERED PREFETCH)
|--Nested Loops(Left Outer Join)
| |--Nested Loops(Left Outer Join, OUTER REFERENCES:([PDMSoccerSQL2000].[dbo].[MATCHmagTeams].[RefNo], [ubl].[bli_UniqId], [Expr1036]) WITH UNORDERED PREFETCH)
| | |--Nested Loops(Left Outer Join)
| | | |--Nested Loops(Inner Join, OUTER REFERENCES:([PDMSoccerSQL2000].[dbo].[MATCHmagTeams].[RefNo], [Expr1035]) WITH UNORDERED PREFETCH)
| | | | |--Nested Loops(Inner Join)
| | | | | |--Index Seek(OBJECT:([PDMSoccerSQL2000].[dbo].[Teams].[PK_Teams]), SEEK:([PDMSoccerSQL2000].[dbo].[Teams].[TeamId]=[@TeamId]) ORDERED FORWARD)
| | | | | |--Clustered Index Seek(OBJECT:([PDMSoccerSQL2000].[dbo].[MATCHmagTeams].[IX_MATCHmagTeams_TeamID]), SEEK:([PDMSoccerSQL2000].[dbo].[MATCHmagTeams].[TeamID]=[@TeamId]), WHERE:([PDMSoccerSQL2000].[dbo].[MATCHmagTeams].[MatchIDno]=[@FixtureId]) ORDERED FORWARD)
| | | | |--Clustered Index Seek(OBJECT:([PDMSoccerSQL2000].[dbo].[TeamSquads].[PK_TeamSquads]), SEEK:([PDMSoccerSQL2000].[dbo].[TeamSquads].[RefNo]=[PDMSoccerSQL2000].[dbo].[MATCHmagTeams].[RefNo]) ORDERED FORWARD)
| | | |--Index Seek(OBJECT:([MyFootballYear].[dbo].[BlockLineups].[IX_BlockLineups_3] AS [ubl]), SEEK:([ubl].[bli_UserId]=[@UserId] AND [ubl].[bli_FixtureId]=[@FixtureId]) ORDERED FORWARD)
| | |--Clustered Index Seek(OBJECT:([MyFootballYear].[dbo].[LineupRatings].[IX_LineupRatings_1] AS [ulr]), SEEK:([ulr].[lr_BlockLineupId]=[MyFootballYear].[dbo].[BlockLineups].[bli_UniqId] as [ubl].[bli_UniqId] AND [ulr].[lr_PlayerId]=[PDMSoccerSQL2000].[dbo].[MATCHmagTeams].[RefNo]) ORDERED FORWARD)
| |--Index Seek(OBJECT:([MyFootballYear].[dbo].[BlockLineups].[IX_BlockLineups_3] AS [tbl]), SEEK:([tbl].[bli_UserId]=[MyFootballYear].[dbo].[GetUserTemplateUserId]([@UserId]) AND [tbl].[bli_FixtureId]=[@FixtureId]) ORDERED FORWARD)
|--Clustered Index Seek(OBJECT:([MyFootballYear].[dbo].[LineupRatings].[IX_LineupRatings_1] AS [tlr]), SEEK:([tlr].[lr_BlockLineupId]=[MyFootballYear].[dbo].[BlockLineups].[bli_UniqId] as [tbl].[bli_UniqId] AND [tlr].[lr_PlayerId]=[PDMSoccerSQL2000].[dbo].[MATCHmagTeams].[RefNo]) ORDERED FORWARD)
(25 row(s) affected)
StmtText
UDF: GetUserTemplateUserId
CREATE FUNCTION [dbo].[GetUserTemplateUserId]
(
@UserId INT
)
RETURNS INT
AS
BEGIN
-- Get the user's selected team id
-- Work out the template user id for the user
DECLARE @TemplateUserId INT
SET @TemplateUserId = (
SELECT tu.us_UniqId FROM Users us
INNER JOIN Users tu ON tu.us_SelectedTeamId = us.us_SelectedTeamId
WHERE us.us_UniqId = @UserId
AND tu.us_TemplateUser = 1
)
(2 row(s) affected)
StmtText
|--Compute Scalar(DEFINE:([Expr1007]=[Expr1007]))
|--Nested Loops(Left Outer Join)
|--Constant Scan
|--Assert(WHERE:(CASE WHEN [Expr1006]>(1) THEN (0) ELSE NULL END))
|--Stream Aggregate(DEFINE:([Expr1006]=Count(*), [Expr1007]=ANY([MyFootballYear].[dbo].[Users].[us_UniqId] as [tu].[us_UniqId])))
|--Nested Loops(Inner Join, WHERE:([MyFootballYear].[dbo].[Users].[us_SelectedTeamId] as [us].[us_SelectedTeamId]=[MyFootballYear].[dbo].[Users].[us_SelectedTeamId] as [tu].[us_SelectedTeamId]))
|--Clustered Index Seek(OBJECT:([MyFootballYear].[dbo].[Users].[PK_Users] AS [us]), SEEK:([us].[us_UniqId]=[@UserId]) ORDERED FORWARD)
|--Clustered Index Scan(OBJECT:([MyFootballYear].[dbo].[Users].[PK_Users] AS [tu]), WHERE:([MyFootballYear].[dbo].[Users].[us_TemplateUser] as [tu].[us_TemplateUser]=(1)))
(8 row(s) affected)
StmtText
RETURN @TemplateUserId
(1 row(s) affected)
[MyFootballYear].[dbo].[Users].[PK_Users] AS [tu]),