No, stored procedures can not be called as part of a select statement.
You can either create a temp table and load the data from the output of the stored procedure into the temp table, then join to the temp table, or convert the stored procedure into a function which can then be included as part of a select statement.
Not quite true…
It can be done by setting up a linked-server reference and then use OPENQUERY. See books online.
Example: SELECT * FROM OPENQUERY(linked_server name, ‘EXEC stored procedure name’)