I have four clients, each with their own identical database.
They all have the same Stored Procedure named sp_Test.
To simplify things, I want a new database with sp_Test, that is called by the clients.
So,I made a new database called COMMON, and put sp_test in there. Problem is, this Stored Procedure doesn't know on which database to perform its actions. A statement like USE DB_CLIENT_ONE gives an error ("USE database statement is not allowed in a procedure").
It's annoying that such a Stored Procedure can do the trick, for example:
SELECT * FROM DB_CLIENT_ONE.DBO.orders
SELECT * FROM orders
this doesn't work
Is there a way to get it done, other than with the EXEC statement?