creating one central sp for multiple databases

Hi.  This is a  "related question" about a situation as follows:
I have multiple databases with identical structures.  I want to have one central stored procedure that can be used in any of my databases. What I would really want to do is have one sp and in it say "use DB1" or "use DB2" based on a parameter or something.  But the use statement cannot be used in an sp.

There is already a (Very long) post about this.  I am interested in this answer from creeds0001::
"... All   of that being said, put the stored porcedure in the MASTER database.  If you put them anywhere else calling them will cause them to use the data in their resident database.  The MASTER database is different.  You might want to give them some special kind of name to help keep track of them, but this will allow you to call the procedure useing your current databases data."

I don't understand how this works.  Based on the above post, I tried the following:  

create procedure MasterTestSP as
select * from mytable

I created the procedure MasterTestSP in the Master Database. Master db does not have  a  table "mytable".  
I went into one of the user databases, StandardDB which has a table "mytable" and ran
exec master.dbo.MasterTestSP

I got the message "Invalid object name mytable".
Which makes sense because it's looking at the master database, not at the child database.

So basically, I don't understand the post. How do I store one central sp in Master and point it to the calling database at runtime?


Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Aneesh RetnakaranDatabase AdministratorCommented:
try creating that on Master database and make that system procedure
EXEC sys.sp_MS_marksystemobject  MasterTestSP

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
AlizaNAuthor Commented:
use master
create procedure MasterTestSP as
select * from mytable

exec sys.sp_ms_marksystemobject mastertestsp

use simpledb
exec mastertestsp
"could not find stored procedure MasterTestSP"

exec master.dbo.mastertestsp
"invalid object name mytable"

what am i doing wrong? did i set it up correctly? how do i call it?

AlizaNAuthor Commented:
ok i got it you have to preface it with sp_ for some reason
sp_mastertestsp works
mastertestsp doesn't.

thanks so much, this is really terrific!
Anthony PerkinsCommented:
>>ok i got it you have to preface it with sp_ for some reason <<
Right.  You have just defined it as a system Stored Procedure, so it has to use the prefix sp_
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.