Using one sproc to go against 10 different tables means that you would need to use dynamic sql rather than staight sql. This means that the caching of the query plans would need to be recalculated each time the sp is run. That is extra overhead that will ultimately cause a performance problem.

As far as the sp_ goes. The only sp's I write with sp_ go into the master database as utilities. In this way I can call them from any database. I do this with for things like sp_select (you can find this in the samples here). That way no matter what db I'm in it will still work. However I would never use sp_ or just sp but instead use usp_ to prefix my database specific stored procedures. This then allows you to easily find your sp's when using the object browser while allowing system objects to be shown.