Search code examples
sqlsql-serverstored-proceduresdynamic-datasynonym

Accessing data from another database in stored procedure

Following is my schema detail:

  • DB_A : schema_1, schema_2, schema_3
  • DB_B : schema_3

some procedures in schema_3 access resources(table, view, sp) from schema_1 and schema_2.

All procedures in schema_3 are same on both the dbs. How do I access schema_1 from schema_3 for both the dbs.

Now I can hard code DB_A in my procedures but when I move code to client machine, it will create a problem since DB_A may not be same(one of the reason being client is miser and having QA, Dev and Prod on same machine).

Second option is getting DB_A name as a parameter, but it will make all the schema_3 SPs dynamic (as I did not get any method to access something like @DBName.schema_name.ResourceName).

Third option is creating linked servers, which again do not solve my problem because of same reason as first.

Any idea how to proceed, where I do not want my procedures to be dynamic just because 80% of them are straight.

Edit Start:

So I can restate it as I have multiple databases with a database having resources (table/view/schema) which needs to be shared and then having other databases (one or more) which have stored procedures which computes on data from shared database and self database.

Shared database name is not going to be constant on all the environments and I want to change them(environment specific). I have come out with a solution where I will be creating synonym for all the shared resources and all procedures will be using them, that way they are all referring to shared resources from first database.

For each installation I need to modify synonyms definition to reflect correct share database name. Is there any SYNONYM For Database Name, that way I will have way less synonyms to handle.

Solution

  • Well the best choice I found is as follows.

    Create Synonym (independent database DB_B) for individual objects (in shared database DB_A) with same name in same schema. That way your existing procedures need not change, and will work as required. Synonym gives a good reference on this. I will soon be creating an app to ease creating synonyms for these kind of situations.

    CREATE SYNONYM DB_B.schema_1.proc_1 FOR DB_A.schema_1.proc_1