The way to learn advanced SQL is to challenge yourself to find a set oriented solution and avoid procedural code. The more unreasonable it feels, the more you learn.

If the solution you find is longer and not much faster than the procedural alternative, you throw it away and fall back on procedural code.

Stored procedures are not advanced SQL. Most of them are not SQL at all. There are a few legitimate reasons for using SPs such as reducing roundtrips to the database and writing little pure functions for use in SQL statements.

But many uses of SPs are just laziness or a symptom of organisational dysfunction.