Any way to make stored procedure code dynamically reference other databases, without dynamic sql?

Question

I've a situation where I have two databases:

• ABCData
• ABCUsers

One of the stored procedures in ABCData needs to perform an insert to a table in ABCUsers. This could just be hard coded:

INSERT ABCUsers.dbo.Table1 VALUES('a');

But the next problem we run into is that we want to backup and redeploy these database pairs to similarly named other databases for testing and dev purposes:

• XYZData
• XYZUsers

Which means the XYZData database will contain a sproc that doesn't work - because the insert has a hard coded ABCUsers.dbo.Table1, and we don't want to get into changing it

We could find out the current database name in the sproc, adjust it and run the sql dynamically:

DECLARE @DataDbName sysname = (DB_NAME());
DECLARE @UserDBName NVARCHAR(20) = (SELECT REPLACE(@DataDbName, 'Data', 'Users'));
EXEC CONCAT('INSERT ', @UserDbName, '.dbo.Table1 VALUES(''a'');')

But I think the prospect of doing so actually makes me feel physically ill ;) (not least because this is a massively simplified example of tens of sprocs each making tens of calls to other dbs)

Is there a way to alias a database name during its creation, so the sproc can always say something like:

INSERT UserDbAlias.dbo.Table1 VALUES('a');

And when the ABCUsers database is created, the UserDbAlias -> ABCUsers but when XYZUsers db is created UserDbAlias -> XYZUsers

---

Alternatively, this is a git/tfs source control - is there a way to write the source control version of the sproc so that it is modified dynamically when it's deployed, and the original source can contain some kind of placeholder for the db names, but they're replaced with actual hard coded varying db names upon deploy (automatically)

---

Footnotes: I looked at A way to reference other DB without hardcoding its name which does seem to describe my problem, but doesn't have an answer. That question mentioned "I've looked at synonyms but they wouldn't work out with EF6.." (paraphrase)

Would synonyms work out for me? We use EF6, but I'm not sure what the actual problem is in terms of why it wouldn't work out. Our sproc name in the db never changes, and we don't need to alias the sproc itself, it's the insert table name we need to alias. If the sproc is always called Sproc1 regardless of whether it's in ABCData or XYZData, and the synonym resolving happens inside the sproc, would EF6 even have a problem with it?

Answer 1

Apart from design considerations raised in the comments, ...

Consider one level of indirection prior to creation of the stored procedure:

• write a template for the stored procedure, with token placeholders for the schema names

INSERT DBNAMETOKEN.dbo.Table1 VALUES('a');

• write a pre-deployment script (sed?) that creates a new file specifically for each schema, substituting the actual schema name for the token.
• place resulting scripts in per-schema deployment directories, from which they may be installed as usual.

Benefits

• Scales well
• Both template and custom versions may be under version control
• Updates/Fixes may still be deployed through a single change to the template file and propagated through a build step
• Possibly easier to schedule/track deployment of updates to each schema depending on situation
• Reduces the stored procedure to a simple(r) set of SQL focussed on the task at hand, rather than the dynamics of making it work across different schema.