Use LEFT OUTER JOIN to the lookup tables.

In the past, table variables would have been another red flag. But SQL Server 2019 did improve row estimates for table variables, so they aren't automatically suspect.

Temp tables created in...

With cursors, I think it's usually not about unpredictable plans so much as whether it's more efficient to do many, many small transactions vs. a few larger transactions. That said,...

Is it critical that it completes in 12 seconds instead of 24? Are you experiencing blocking or other issues due to the longer execution time? If not, and especially if...

Retracted following OP response

Parameterize your subcategory parameter available values query to use CategoryID parameter as a filter:

SELECT  SubCategoryID, CategoryDesc
FROM #sc
WHERE CategoryID = @CategoryID

You will set the Dataset parameter @CategoryID parameter...

Looks like a repeat of

https://www.sqlservercentral.com/forums/topic/pivot-split-column and

https://www.sqlservercentral.com/forums/topic/pivot-split-column-2 and

https://www.sqlservercentral.com/forums/topic/pivot-split-column-3

Here's an approach using SSIS that looks promising: https://social.msdn.microsoft.com/Forums/SECURITY/en-US/8c114455-19c3-405f-8234-c83690dc3450/ssrs-export-separate-pdfs-from-ssrs-generated-report-subscriptions?forum=sqlreportingservices

And an approach using Powershell: https://www.sqlservercentral.com/forums/topic/ssrs-export-to-separate-pdfs

Because, as far as I know, SSRS can't export pages as separate files, if you want to...

The right answer would indeed be to have <Entity>Note tables. I wouldn't make those tables just an association table to Note table w/ a NoteID -- I would actually put...

Why do you need a separate calendar for each student?

Couldn't you just make Student_id a parameter for the report & query?

Is SETPARM your own stored procedure? I have never heard of "SETPARM" in TSQL.

It's possible manually, of course.

You could probably script out all stored procedures as ALTER scripts, & then...

When you say "PDF is within a SQL Database" do you mean as a blob (e.g., varbinary column)? You can't hyperlink to a location that doesn't exist.  Or are you...

Your company appears be addicted to out-of-support SQL versions. SQL Server 2014 reached its end of life on July 9, 2019 and its extended support will end on July 9,2024.

The...

However, when running on a big dataset I start getting an error "Conversion failed when converting date and/or time from character string."

Unfortunately it means what it sounds like -- there...

Looks like it: https://docs.microsoft.com/en-us/sql/relational-databases/track-changes/about-change-data-capture-sql-server?view=sql-server-ver15

It appears to leverage some of the same processes... but this may refer to the publisher rather than the subscriber:

The logic for change data capture process is...