Execute instructions from RAM in a hobbyist-friendy microcontroller

Another option to consider is using an interpreted language for your programs stored on the SD card. This way, the processor isn't executing the machine code read from the card, it's just treating it as data.

This approach gives you flexibility at the cost of speed.

There are plenty of options to choose from: Survey of High-Level Language Interpreters/Compilers for Microcontrollers

As has already been said, PICs (other than the PIC32) cannot do this. You will probably have to go to the larger processors in any family or to a processor with an external memory bus as most microcontrollers have very limited RAM resources.

The MSP430 processors can run code from their RAM space but you will need something like the F5438 with 16k RAM space - running code in 128 bytes is not really an option!

If a processor has an external bus then you can put RAM into the code space. You may have to add some extra logic to map the RAM into two memory regions if the processor architecture does not allow data to be written into execution memory.

I have run code from RAM in an 8051 based system but that meant that the RAM had to be mapped into the EXTERN memory space for programming and then back into the CODE space for execution. The loader/monitor program handled the memory bank switching and loading. Please don't ask for the code - I did this about 30 years ago and it is long since lost (and written in PL/M-51)

None of the low and mid range PIC can execute from RAM because of their memory architecture.

Any ARM based CPU should execute from RAM. Although they tend to be in smd packages, there are quite a few 'DIP' size module that have the microcontroller already loaded. Have a look at the mbed or LPCXpresso for example. These both come with either a bootloader or, in the case of the LPCXpresso, a debug interface along with free compilers.

If you would prefer to stay with simple 8 bit micros, perhaps consider something from the freescale HCS08 Family. These can execute from RAM and there is a code limited version of the codewarrior IDE and C compiler available for free.

I'm fairly sure the MPS430 should also be able to do this, but I've not ever done it myself.