We have stored procedures that access tables across databases. For example, we might have CustomerDev and OrderDev databases and the following query exists in a sproc on CustomerDev database (simplistic example):
SELECT * FROM [dbo].[Customer] c
INNER JOIN [OrderDev].[dbo].[Order] o ON c.orderxid = o.xid
Now say we have CustomerTest and OrderTest databases (same databases as Dev but for Test environment application to use) on the same physical database server.
If we promote that same stored procedure to Test databases, the sproc is still hitting the OrderDev database instance instead of OrderTest.
This seems like it would be a common issue for any startup company or small project that needs multiple environments for promoting but only has one database server because its a shared hosting account or we can only afford one right now or etc.......
We don't want to use dynamic SQL statements to do this, we lose too much of the native SQL optimization features and its worse performance and a maintenance headache.
The only other solution we can think of is to have a case statement detecting the database name which we would use to determine the environment, and then duplicate the sql logic in eadch case or if...else statement. This is not ideal because of the amount of repeat code, also a maintenance nightmare.
Does anybody have a good solution for this issue?