SD card as ROM memory in a microcontroller
So it depends on what you want to do.  As a general rule: if you're willing to run slowly enough, you can do whatever the heck you want.

On microcontrollers (like a PIC or an ATMega processor (not including PIC32 or Atmel's ARM processors)) you normally have a Harvard Architecture which means that code and data are stored in different parts of memory and accessed differently. This means that if you load up a piece of code from an SD card, it will be in RAM, and you will not be able to treat it as code without some special acrobatics. You can write the loaded information to the internal code banks (which is how bootloaders work), but essentially you'll never be able to have more code loaded than what your microcontroller started with for code size limits.  Now, you could (with some effort) write special "call" and "return" features in your code which essentially were functions which would load the requested address out of the SD card and write it into the internal code banks and then call the newly loaded code.  This would necessitate that every function you had was smaller than the internal code banks, but that wouldn't be hard.  The tough part would be that this would cause a lot of wear on the code sections of memory in your chip, which are frequently only rated for ~100,000 writes (or as mentioned below, as low as 10,000 writes!).

On most Von Neumann architecture (and Hybrid-harvard) machines (ARM, Mips, and x86 as well as others), data and instructions are interchangeable.  This means that you can much more easily write code to load the instructions out of the SD card and into ram, and then start running.  If you don't have as much RAM as you have code (which, based on your original premise sounds unlikely), then you'll still need to do some tricky stuff to either repeatedly call to load from SD card into RAM, just like the previous example called to load from the SD card into program memory.

If you have a Von Neumann architecture and are lucky enough to have a microcontroller with a memory management unit or some sort of software-defined paging mechanism (I think they have this now but I don't know what it is), then you can start applying virtual memory concepts.  You'll put all of the code into RAM, and page out code to the SD card.  This is super complex if not already implemented on/in your microcontroller, so I won't go through explaining it unless you think this is what your planning to do (I'll need to look some of the ARM-based software paging mechanisms in order to help).  This one requires the fewest changes to your initial code, but essentially requires an operating system on top of your code to handle code page switches.

Hope that helps!



EDIT:

As many have mentioned in the comments below, perhaps your best bet is to build an interpreter which fetches instructions from the SD card and executes them using code in the internal ROM.  This works for both Harvard and Von Neumann architectures, as you have code which is permanent which executes based on data from the SD card.  There are several implementations of this already in existence: see Basic Stamp or NanoVM for some jumping off points.  Understand that this will, of course, always be pretty slow.  And if you don't want to use a pre-made example of this you'll be caught making your own compiler/tokenizer as well as the interpreter, which will lead to a lot of complicated debugging.  Upsides to this is that once you have it all working you can port your interpreter to any architecture and you'll be ready to go very quickly!