How do i Return a value from a stored procedure in the select statement of another query

Lets say for example that i have the following command

select column1, column2,column3, (select count(*) from tableb) as column4
from tablea

Now lets say that my real query was far larger, and far more complex and i wanted to place column4 as a stored procedure or function.

Is this possible? and are there any drawbacks?

Thanks