Sharing stored procedure across database using synonyms

Question

I have two different SQL Server databases (on the same server - if it helps) that need to share the same stored procedure logic. The solution I'm trying to achieve looks like this:

Database1
  Table: TestTable
  Synonym: sp_MyProc pointing at SharedDatabase.dbo.sp_MyProc

Database2
  Table: TestTable
  Synonym: sp_MyProc pointing at SharedDatabase.dbo.sp_MyProc

SharedDatabase
  Proc: sp_MyProc which runs queries against TestTable

My hope was to use the synonyms so that if I execute sp_MyProc while in the context of Database1, it would use Database2.TestTable. And if I execute sp_MyProc while in the context of Database2, it would go against Database2.TestTable. However, when I execute sp_MyProc through either of the synonyms, it ignores the context of the synonym and executes looking for a local copy of TestTable, which is not found.

Is there a way to implement a shared stored procedure that executes against different copies of tables in different databases, either through synonyms or some other mechanism?

Edit
I should mention that in my case I am looking to do this with a large set of existing tables and procs, so any solution that requires modifying the procs or tables themselves are not ideal.

Answer 1

Something like this would work for the definition of the procedure. Be sure to guard against SQL injection since this is built dynamically.

CREATE PROCEDURE [dbo].dosomething 
  @databaseName sysname,
  @schema sysname,
  @tableName sysname
  as
  declare @cmd as nvarchar(max)
  set @cmd = N'select * from ' + quotename(@schema) + N'.' + quotename(@tableName)
  exec sp_executesql @cmd

Then use it like this:

  dosomething 'SampleDb', 'dbo', 'sampleTable'

Answer 2

If the stored proc is in the SharedDatabase, then it will always run in context of SharedDatabase. To accomplish what you are trying to do to centralize code, I would maybe pass in a parameter to designate which server it is coming from, so then you can execute the query against that specific TestTable. Basically, you will need to refer to each table using their fully qualified name - i.e. Database1.dbo.TestTable

  USE SharedDatabase

  CREATE PROCEDURE [dbo].sp_MyProc 
  @dbsource varchar(50)
  as 

  if(@dbsource == 'DB1')
  begin

    select * from Database1.dbo.TestTable

  end
  else
  begin

    select * from Database2.dbo.TestTable

  end


  GO 

The other alternative is to make a view in SharedDatabase, which will be called TestTableComposite, with an extra column to identify where the source data is. And then pass that in as the parameter, and your SP on SharedDatabase will always be in context of that DB.