Return a dataset from a stored procedure?

Does anyone know how or if a stored procedure or function can return a dataset similar to using select statement.

ie.
Instead of this
  SELECT * FROM EMP
use this:
  SELECT * FROM <STORED PROCEDURE>

-- 
Regards,
  Scott Dayton