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
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?