Stored procedure language is specific to your RDBMS. Try running your stored proc from the command line or from a GUI admin tool. If you're lucky this might give you a clearer error message.
A Select statement generally returns a set of results, even if the actual set is a singleton value. I'm taking a guess, but you might need to put parentheses around your select statement to indicate to the parser that it is returning a single value instead of a set.