Don''t blame SQL please....
-
Okay, here is one that bugs me and I hope someone has a more graceful solution! How can I avoid writing, maintaining, and running dynamic queries when I want to change a db name? Let's say I have a stored proc which pulls data from another database (let's call them db1 and db2). So I may use a call from db1 like the following:
SELECT a.col1, b.col2 FROM db1.dbo.tbl1 a INNER JOIN db2.dbo.tbl2 b ON a.col1=b.col1
Now because I have multiple dev and test environments, db1 and db2 may have different names on different servers depending on the release on which we are working. So for our Release 2, we might have db1_R2 and db2_R2. Right now, I use an execute statement and send in a variable to determine the name at run time. But it is a pain - particularly on lengthy queries with lots of literals
! I wish I could just alias the table names with a varaible at the beginning of the proc and just use that variable throughout the rest of the proc.Beth
-
There are many solutions.
One of them:
Build a table UDF with parameter @DBName.
Another one:
Use dynamic query to BUILD A VIEW addressing your databases.
_____________
Code for TallyGenerator
Viewing 2 posts - 16 through 16 (of 16 total)
You must be logged in to reply to this topic. Login to reply