- I have a database called REFERENCE. It stores my functions and stored procs,
e,g, procDoSomeStuff() with code like SELECT stuff FROM localDatabase;
- I have a multitude of databases containing data.
I am in DATABASE1 issuing a long list of queries, some using functions.
e.g. CALL REFERENCE.procDoSomeStuff;
Problem is, REFERENCE.procDoSomeStuff() thinks that all its queries should be executed in the scope of REFERENCE.
It looks for REFERENCE.localDatabase rather than DATABASE1.localdatabase as I was expecting: in other words, it looks like REFERENCE procedures are not really "loaded" into the DATABASE1, they spin off their commands into REFERENCE. Weird.
So how do I "parametize" the database name? How do I modify my SP and functions in REFERENCE to accept a database name I supply? Or if it is easier, convince that all the code in a function should run in DATABASE1
I have lots of databases, but all use the same functions and stored procs, hence the need to share them in a common database I can call.