Basic Mechanics
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.
Spells
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.
Why?
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.
Choices
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.
Homebrews
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.
Two reasons.
First, the various horrors and Things Man is Not Meant to Know that populate the universe in a Lovecraft Mythos game are not organised enough (against us, at least) to pull this kind of thing off as a regular tactic—if they were, the world would already be consumed, enslaved, or worse. Since we're playing a game where we investigate and try to stop that kind of fate, we can presume that the horrors haven't gotten it together enough, or just haven't bothered yet, to destroy the world. Teleport-bombing is small potatoes: why bother when it's easier to just eat the planet?
Second, they just don't think like people do. What makes sense for us as a sure-fire strategy just doesn't matter to them. It's much like a fly beating at a window (where we are the fly in this analogy and the horrors are the humans): it's obvious to the fly that the solution is to go straight through that unobstructed opening in the wall! Why don't the giant pink creatures that we're fleeing from, which clearly want to eat us, pursue us? We're getting away!
Except the fly isn't getting away, it's trapped. And we don't care. And our motives are certainly not comprehensible by the fly. Also, maybe we're not organised enough to deal with the clear and present danger that this insect poses to… our plans for getting ready for work and finding our keys? Maybe we'll take a half-hearted swipe at it while we go by, but unless the fly swatter is ready to hand we're just going to get on with our day and let the fly die in the window in its own time. A spider (i.e., Shoggoth, etc.) will probably get it for us anyway.
Humanity, and the whole Earth really, is like that fly to the Lovecraftian horrors. They're inimical to us, yeah, just like people really don't like flies in their house. But they're not going to bother with eradicating us unless it's convenient, they usually don't think about us, and the only time they'll actively go after us is when we pretty much fly straight into their "hands". If their incomprehensible cosmic plans call for it, they'll get organised and eradicate us, but why would they bother otherwise? Are you really going to fumigate your house every time a fly gets in? No, you're only going to bother with a major infestation, or when trying to sell the house in a tough market, or something like that. And how much can that fly in the window grasp about these factors that control its fate and continued living? None. It just beats against the glass.
Event that analogy is flawed, because it attempts to parallel their thoughts to ours. The useful take-away part of the analogy is that we're insects to them, not worthy opponents that deserve much attention or efforts to teleport-bomb, and that our intellects and comprehension of what is sensible is so disconnected from the true nature of reality that we can't explain or predict their actions.
The only adversaries in a Mythos setting that are going to be motivated in a human-understandable way enough to want to teleport-bomb the heroes are other humans, and if they're treating with horrors enough to become dangerous cultists, they're already far gone enough that what they're doing is already quite insane and has incomprehensible motives. Insanity is enough explanation to not do the obvious thing.
Even sorcerers who have maintained enough of their cunning to be somewhat comprehensible in their motives are going to have more going on than merely teleport-bombing some annoying investigators. Being Mythos sorcerers, to do anything mystical they have to get close to those horrific truths of reality that can burn out a mind, and how often can someone grab hot coals to fling at their enemies before they get burned? The more powerful the sorcerer, the more frequently they use their power, the more likely they are to have inhuman motives or to provoke or discover something that will destroy them (or the world). Sorcerers are either going to be self-limiting for their own sanity and safety, self-limiting by being insane and doing incomprehensible things for incomprehensible reasons (see above), or they're going to be self-limiting by earning a Darwin Award.
Best Answer
You have stumbled on the issue (or a primary issue) that prompted the development of the gumshoe system. GUMSHOE is used for a number of games with investigative elements, including Trail of Cthulhu, a GUMSHOE implementation of CoC and Night's Black Agents, a spies-vs-vampires setting with a more militaristic bent. By extension, the solution adopted may work for you, regardless of the system. In a nutshell:
Never make the progress of the game wait for a successful roll
The GUMSHOE answer to this is exactly what's above. Let's get some details on it.
Since it's an investigative issue, it's important that a clue is well defined: A piece of information necessary for players to get to the next scene. That's the GUMSHOE definition and we'll stick with it.
In GUMSHOE, a character with an appropriate skill, in the presence of a clue, who takes an appropriate action gets the clue. There is no roll.
You have a player with a high skill regarding guns (I don't know Delta Green - is it "shooting"? "ballistics"? "slugthrowers"?). The clue is that the bullet holes in the aftermath of the battle are wrong - if they were enemy forces they should be a different calibre - the ambush was by friendly forces! So when your gun-knowing PC says, "I take a look at the bodies to see if anyone is still alive" or, "I try to figure out where the ambushers were located by checking out the trajectories in the rubble" or whatever, you give her the clue.
That's it. Now, there might be more information, juicy details like the fact that the rounds are teflon-coated, a sure sign of Major Tambert's involvement, or whatever. Go ahead and let extra goodies like that be rolled for. They give the players rewards for being good at what they do.
But never stop the game from moving forward for lack of a successful roll. You're just frustrating everyone while they wait for that "live wire".
This isn't about just giving the clue away. There are two conditions that must be satisfied before the clue is given:
This provides some measure of flexibility. As GM, you know the nature of the information and therefore what actions would reveal it. You don't just give away the clue for walking into the space. You wait until something has happened that would reveal the clue.
The point is that finding the clue is boring. It's what they do once they know that's exciting. A clue can still be hidden. Papers could be locked in a safe, that safe could require safecracking or demolition to get inside. But if the forensic accountant gets her hands on the papers, and says, "I look for unusual activity in these accounts!" give her the clue - do not roll to see if the forensic accountant succeeds at this time.
BTW - In the above example, if a failed demolition roll destroys the papers, there had better be another way to get the information (or some information that leads to another scene) or you're just as stuck as if you hadn't provided a clue in the first place. A better result for a failed demolition roll has some other negative impact - it takes all of their detcord, for example, but still gets the clue free.