Question: As above, what is the reason for needing a memory module on a RAID card?

The card I am looking at (HP P410) comes with either 256MB or 512MB but unsure which to get and why.

Answer: 
The purpose of RAM on a RAID controller is to provide a level of cache to allow write operations to hit stable storage without incurring the latencies involved with rotating disks. 

The RAM on the controller can usually be split between read and write cache. A common ratio is 75:25 write:read. 

Write caching is helpful because applications can write to low-latency controller memory with no delay. That data then gets flushed to disks, which are an order of magnitude slower.

Read caching helps keep hot or frequently-used data on the controller's memory so that requests don't need to be fetched from slower disks.

For your HP ProLiant server and Smart Array controller, you will want a larger cache *and* at battery-backed or flash-backed unit for it (aka. BBWC or FBWC). Those improve performance by enabling the write accelerator tied to the cache module. The write accelerator is disabled where there's no BBWC or FBWC available.

See the following questions for specific scenarios and use-cases.

[Non-volatile cache RAID controllers: what kind of protection is there against NVCACHE failure?](https://serverfault.com/questions/401488/non-volatile-cache-raid-controllers-what-kind-of-protection-is-there-against-nv)

[Incredibly low disk performance on HP ProLiant DL385 G7](https://serverfault.com/questions/221541/incredible-low-disk-performance-on-hp-dl385-g7/221553#221553)
Aside from the read/write cache, without the memory, some RAID controllers disable some functions like array expansion. It's definitely better to have it, than to not.