Stored procedure prepared statement no error and not working
Doh, I looked at numerous examples online, and these were pretty much the same as I used in my example, however I did think I was missing something, then I found another example which proved that I was missing:
EXECUTE stmt;

So the complete prepared statement looks like:
PREPARE stmt FROM @SQL;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;