The majority of exploits I've seen using the /tmp directory included uploaded binaries. There is still a way around the noexec option with binaries, but it does make it a bit harder. Nonetheless, nothing should be executed in /tmp so there is no reason to leave that option off. I like to set nosuid as well.
mount --bind /tmp /tmp
mount -o remount,noexec,nosuid
Script kiddies will usually give up if the process is too complex to exploit

This is why we call them script kiddies and not hackers.
I think the only way to prevent any code from being executed in /tmp is by using ACLs (such as with grsec).