I am trying to figure out how to reference multiple database/objects in stored procedures.
I know how to create dynamic SQL and exec() etc. but I don't think that will help here as I am dealing with cursors etc. (although I could do without the cursors with MUCH rewriting).
Essentially, a given server will have multiple databases that are identical schemas, different data, and, a utilities schema/db where the generic objects live.
i.e. (greatly simplified and renamed, etc.)
I would like to have a single stored procedure in [Utilities] that takes a parameter (or more likely reads from a table) for which Company Db will be referenced....
The sp pulls and relates data from Utilities to CompanyX data, and does a bunch of stuff with it, and populates [Utilities] tables....
so right now, I have a (nearly) identical sp in each db....
I would like to have one to maintain/test in [Utilities] and do;
exec [utilities]..[MyProc] 'CompanyDb1', 'a value', 'anotherparamvalue'....
exec [utilities]..[MyProc] 'CompanyDb2', 'a value', 'anotherparamvalue'....
exec [utilities]..[MyProc] 'CompanyDb3', 'a value', 'anotherparamvalue'....
How should I do this ?