Call stored procedure in another database

[k8277]

I am stuck here.

I have 3 databases, A, B and C. Databases A & B contain my data for two different locations. Database C contains my custom stored procedures and functions. I would like to be able to call a stored procedure which resides in database C from database A. I want the stored procedure code to run against my data in database A.

I then would want to be able to call that same stored procedure from database B. Eventually, I could have several additional databases and I am trying to keep from having to create the same stored procedures in each and every database. My goal is to have one database C, which contains all of my utility procedures and functions.

My problem today is that when I call the stored procedure in database C, it is running against the data/tables in database C not database A. I also would prefer not to hard code object names in my stored procedures. Can this be accomplished with passing parameters to my sp in database C that will basically tell it where the data is?

Thanks

 

[RiverGuy]

There are a few options you could use:

1. Use dynamic sql to build a sql statement to reference the correct database based on a parameter (bad idea).

2. Create views in database c which UNION ALL's the data from the different databases. Your stored procedure filters on the database by utilizing a database name or value column in the view (could be a performance killer).

3. Come up with a change policy where you push out stored procedures to all of your databases, executing the appropriate stored procedure in the appropriate database (my recommendation).

 

[gmmastros]

4. Create a synonym in database c which points to the correct database. (assuming you are using SQL2005 + )

http://www.developer.com/db/article.php/3613301

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[RiverGuy]

So then I guess you would add a dynamic SQL statement to the procedure to create the synonym based on the parameter? Or did you have another way?