I think this is where you can draw from MMOs for various kinds of usable spells that aren't straight save or suck, but do have effects.
Stuff that lowers movement speed, stuff that has a chance at preventing attacks or actions at a percent, stuff that can lower derived stats(like attack skill, flight ability, leadership skill, etc, etc.
Things that can hinder and generally make a players actions less powerful, but don't outright prevent them.
Then simply scale those effects in relation to the casters skill vs the persons resistance.
If you're working off D&Ds save system, just have the save be compared in relation to the spell, rather than a strict pass/fail.
To use the Despair Aura example, say if you fail by 20 or more you're just dead, but then it has weaker effects as you get closer to your average save.
You can then advantage some otherwise weaker effect spells by giving them better save spreads.
Maybe "Weak Paralyze" doesn't inflict as strong of an effect as normal "Paralyze" does, but Weak Paralyze still applies it's effect even if they save, as long as it was by 5 or less.
This might be getting too deep into detail for the CYOA, but the core idea of spells having scalable effects based on their enemy is good because it can strike a compromise between being totally neutered and being totally pointless in effect.
It also devalues immunities as a choice, because as the effects aren't so overtly terrible there's less need to be totally immune to them.