The mechanical difference of these systems is spelled out in the first paragraphs of each in the BRP core book. Magic is on page 89 and Sorcery is on page 122. In a very generalized nutshell, Magic represents characters being able to learn the very basics of how spell casting works in general, and that knowledge enables them to learn individual spells. These spells are individually treated as skills with their own percentage chances for successful casting. In contrast, Sorcery allows the spells cast by the sorceror to be cast automatically providing the conditions for casting are met.
While the spells from each tradition of magic may not be seen as having more potency or greater effects, the Sorceror is a character who has both the high requirements for the rigours of the art, and has taken the time to devote significant training to the art of spell casting. [A larger arsenal of memorized spells, high POW score, spells are cast without a roll]. Both paths allow the dedicated devotee to learn new spells to add to their repertoire, create and maintain spell books, devise new spell formulas, and use external sources of Power Points for casting. In essence, the mechanics attempt to demonstrate that these two paths to magical power represent a lower and higher road, and allow the Magician to be as dedicated to her road, as the Sorceror is to his. The difference is that the Magician is making the best of what she can do with her natural talents, while the Sorceror was born to it. In the end, it does not indicate which of the two will be more powerful - that is an entirely different thing.
In addition to adding flavour and differentiation to what can be done with the game, having a variety of spell-casting traditions allows for characters of different levels of experience and ability get involved in one way or another with parts of the setting that they discover in play and grow attracted to. This also helps represent vast swaths of fiction which groups may wish to emulate.
Another not insignificant point is that different groups grasp or like different types of mechanics or different levels of capability, and BRP is designed as a tool kit, while CoC is designed to emulate a very specific play environment.
I have used a variety of magic systems for cultural and professional reasons in Call of Cthulhu, as well as linking different types of magic to different in-game sources of otherworldly origins, with varying rates of miscomprehension and tangential development by human cultists and practioners over the generations. As long as the player is interested in using mystical knowledge (with all its attendant SAN and attention-getting perils in CoC) and understands the basics of the system for that style, things flow very smoothly. Like any system, burdening a disinterested player with an extra responsibility will lead to dissatisfying results.
Picking one system, or running with several different approaches depends on how well the Keeper (for CoC) understands them, how much they intend to have them add to the atmosphere and investigative approach of the game, and how keen the players are to add this tool to their characters' capabilities. For most campaigns, the magic system in CoC itself works just fine, and needs no expansion. For long-term campaigns, generation games, or campaigns with clearly defined, recurring, mystical villains the added mystique can pay off immensely. Like in Shadowrun and other games with a broad and complex array of different game elements, BRP can run in a stripped down form at first, and then slowly grow along with the group as the mechanics are internalized.
I did fiddle with a system similar to Sorcery prior to seeing other types of spell-casting rules for BRP, but no players took routes which put that sort of knowledge into their hands. The lure of staying Sane can persuade a lot of players to accept that 'there are somethings Man was not meant to know.'
Putting it in Play: Examples from Popular Media
TV: In the Joss Whedon TV Series 'Buffy the Vampire Slayer' Rupert'Ripper' Giles is shown as understanding how magic and spellcasting work, and when required can pore over old tomes to figure out how to use existing spell formulae to cast spells. They don't always work, but if he gets the incantations right, and has the dates right, he can usually get some sort of effect (roll required for spellcasting). By comparison, in later seasons, Willow has discovered talent for spell casting (high POW and high INT) and her studies lead her to master broader areas of spell casting that make her able to use the spells she knows at a much higher proficiency and little chance for failure (barring rolls required on the Resistance Table).
Fiction: Raymond E. Feist's series which began with Magician: Apprentice and Magician: Master slowly revealed a very strong dichotomy of mystical traditions along these same lines with a high and low road of spellcasting that practitioners were naturally predisposed to only one of. In this series, the possible spell effects for each road had some overlap, but were often quite different. An example of this difference is teleportation. Magicians of the low road were able to teleport in a line of sight fashion as far as the eye could see, while Magicians of the high road could teleport to any place that they could visualize clearly in their mind. While both could teleport, ranges, adaptability, and approaches were very different.