Master Moron said:
Winning is fun. Losing isn't.
If it were as simple as that, everybody would just grab a Gameshark and win at everything all the time with no effort.
But yes, it depends on the game. Most modern console RPGs are built so that you can play through the main story without ever stopping and explicitly "leveling," so that's what I tend to do.
If a game does require too much leveling, I tend to get bored and put it down. Final Fantasy Tactics, for example, had a fascinating and deep combat system, but I got so tired of having to play fifty of the same random battle in a row in order to level up for the next big hard "story" battle that I never did finish it.
On the other hand, Dragon Quest 8 is superbly balanced such that you always feel like the next tough boss or dungeon is just BARELY too powerful for you to tackle, so leveling isn't such a chore because you only tend to do a little of it at a time. Plus, you've got this huge beautiful world to explore while you level, with chests and unique monsters to search for, so it gives you something to do rather than just run in circles around a town and dash in whenever you need healing.
That's how leveling SHOULD be, IMO-- you follow the main story line until you reach a road block, and then you go off and complete some side quests until you're powerful enough to progress in the main story some more. That way the main story can retain a sense of challenge, but you never have to just mindlessly level so much that you get bored and the game seems more like a chore than a pleasure.