Sorry but your question is not absolutely clear.
Are you trying to access multiple tables via one single stored procedure .
This can be done easily by using JOINS within the SP.
If you want to return data from multiple tables having a similar structure but don't want to join them, you can use UNION.
E.g.
Create SP1 AS
SELECT Something FROM TableA
UNION
SELECT Something FROM TableB