Must declare the scalar variable error in CTE

Question

I have a lot of CTE in my query and I know it it not a good practice but I need help.

WITH cadet AS
  (SELECT afpsn,
          surname + ', ' + SUBSTRING(firstname, 1, 1) AS cname,
                           SUBSTRING(gender, 1, 1) AS gender
   FROM tblCadets
   WHERE pmaclass = @pmaclass
     AND company = @company),
     a AS
  (SELECT cadet.afpsn,
          AVG(ISNULL(testscore, 0)) AS pft1
   FROM cadet
   LEFT OUTER JOIN tblPfescore ON cadet.afpsn = tblPfeScore.afpsn
   WHERE testname = 'PFT 1'
     AND semester = @semester
     AND sy = @sy),
     b AS
  (SELECT cadet.afpsn,
          AVG(ISNULL(testscore, 0)) AS pft2
   FROM cadet
   LEFT OUTER JOIN tblPfescore ON cadet.afpsn = tblPfeScore.afpsn
   WHERE testname = 'PFT 2'
     AND semester = @semester
     AND sy = @sy),
     x AS
  (SELECT cadet.afpsn AVG(ISNULL(testscore, 0)) AS pabt1
   FROM cadet
   LEFT OUTER JOIN tblPfescore ON cadet.afpsn = tblPfeScore.afpsn
   WHERE testname = 'PABT 1'
     AND semester = @semester
     AND sy = @sy),
     d AS
  (SELECT AVG(ISNULL(testscore, 0)) AS pabt2
   FROM cadet
   LEFT OUTER JOIN tblPfescore ON cadet.afpsn = tblPfeScore.afpsn
   WHERE testname = 'PABT 2'
     AND semester = @semester
     AND sy = @sy),
     ab AS
  (SELECT a.afpsn,
          (a.pft1 + b.pft2) / 2 * (tblPfesub.percentage / 100) AS pftres
   FROM a
   INNER JOIN b ON a.afpsn = b.afpsn
   CROSS JOIN tblPfesub
   WHERE tblPfesub.subname = 'PFT'),
     xd AS
  (SELECT x.afpsn,
          (x.pabt1 + d .pabt2) / 2 * (tblPfesub.percentage / 100) AS pabtres
   FROM x
   INNER JOIN d ON x.afpsn = d .afpsn
   CROSS JOIN tblPfesub
   WHERE tblPfesub.subname = 'PABT'),
     abxd AS
  (SELECT ab.afpsn,
          (ab.pftres + xd.pabtres) AS pferes
   FROM ab
   INNER JOIN xd ON ab.afpsn = xd.afpsn),
     e AS
  (SELECT cadet.afpsn,
          AVG(ISNULL(score, 0)) AS score
   FROM cadet
   LEFT OUTER JOIN tblSfscore ON cadet.afpsn = tblScore.afpsn
   AND semester = @semester
   AND sy = @sy),
     f AS
  (SELECT cadet.afpsn,
          AVG(ISNULL(intrams, 0)) AS score
   FROM cadet
   LEFT OUTER JOIN tblCspscore ON cadet.afpsn = tblCspscore.afpsn
   AND semester = @semester
   AND sy = @sy),
     z AS
  (SELECT cadet.afpsn,
          AVG(ISNULL(collegue, 0)) AS score
   FROM cadet
   LEFT OUTER JOIN tblCspscore ON cadet.afpsn = tblCspscore.afpsn
   AND semester = @semester
   AND sy = @sy),
     h AS
  (SELECT cadet.afpsn,
          AVG(ISNULL(club, 0)) AS score
   FROM cadet
   LEFT OUTER JOIN tblCspscore ON cadet.afpsn = tblCspscore.afpsn
   AND semester = @semester
   AND sy = @sy),
     fzh AS
  (SELECT f.score AS score
   FROM f
   INNER JOIN cadet ON cadet.afpsn = f.afpsn
   UNION SELECT z.score AS score
   FROM z
   INNER JOIN cadet ON cadet.afpsn = z.afpsn
   UNION SELECT h.score AS score
   FROM h
   INNER JOIN cadet ON cadet.afpsn = h.afpsn)
SELECT CAST(ROUND(abxd.pferes, 2) AS numeric(36, 2)) AS pfe,
       CAST(ROUND(a.pft1, 2) AS numeric(36, 2)) AS pft1,
       CAST(ROUND(b.pft2, 2) AS numeric(36, 2)) AS pft2,
       CAST(ROUND(x.pabt1, 2) AS numeric(36, 2)) AS pabt1,
       CAST(ROUND(d .pabt2, 2) AS numeric(36, 2)) AS pabt2,
       CAST(ROUND(e.score, 2) AS numeric(36, 2)) AS sf,
       CAST(ROUND(f.score, 2) AS numeric(36, 2)) AS intrams,
       CAST(ROUND(z.score, 2) AS numeric(36, 2)) AS collegue,
       CAST(ROUND(h.score, 2) AS numeric(36, 2)) AS club,
       CAST(ROUND(MAX(ISNULL(fzh.score, 0)), 2) AS numeric(36, 2)) AS csp,
       cadet.afpsn,
       cadet.cname,
       cadet.gender
FROM a
CROSS JOIN b
CROSS JOIN x
CROSS JOIN d
CROSS JOIN abxd
CROSS JOIN e
CROSS JOIN fzh
CROSS JOIN f
CROSS JOIN z
CROSS JOIN h
CROSS JOIN cadet
GROUP BY f.score,
         z.score,
         h.score,
         abxd.pferes,
         a.pft1,
         b.pft2,
         x.pabt1,
         d .pabt2,
         e.score,
         fzh.score,
         cadet.afpsn,
         cadet.cname,
         cadet.gender
ORDER BY cadet.cname

The above query is not working. When I tried to execute it SQL Management Studio and it only returns an error: Must declare the scalar variable @semester. I review it many times but I can't find the mistake. I need some help.

Answer 1

Just DECLARE a @semester variable before run the query

DECLARE @semester NVARCHAR(MAx)