Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2005-06-21 : 06:41:48
|
| Steve writes "Is there any way to parameterize a database name in a query? For instance, suppose I have a query like this:SELECT COUNT (SVC.ServiceID) FROM PatientDB.dbo.Patient PTINNER JOIN ServiceDB.dbo.Service SVC ON SVC.PatientID = PT.PatientIDI want to be able change one or both of the databases without having to modify the query. The reason why changing the query is not practical is that I have a large set of stored procedures that perform complicated data analysis, and I need to run them against different databases for development and testing. It is very time-consuming and error prone to change all of the queries.I'm familiar with the EXECUTE command and sp_executesql stored procedure, but these approaches don't work with temporary tables. That also have the disadvantage that queries are not syntax-checked when the stored procedure is compiled.I'm using SQL Server 2000. Thanks for any suggestions you can make." |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2005-06-21 : 07:11:47
|
| [code]Declare @DB varchar(30)Declare @sql varchar(2000)set @DB='PatientDB'set @sql='SELECT COUNT (SVC.ServiceID) FROM '+@DB+'.dbo.Patient PTINNER JOIN ServiceDB.dbo.Service SVC ON SVC.PatientID = PT.PatientID'Exec(@sql)[/code]MadhivananFailing to plan is Planning to fail |
 |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-06-21 : 07:21:46
|
| I'm not sure I see the utility in rewriting all of the stored procedures to use dynamic SQL, when it's been stated it's "too time-consuming" to change the database name as it is (which would be a much simpler task).If you are properly developing and testing your code, you should be doing it on completely different servers anyway. That way you do not have to change database names at all. In fact, you cannot properly test your code without such a setup. Making the database name dynamic will lead to broken code anyway, as someone could pass a completely invalid and non-existent name. |
|
|
|
reggiedba
Starting Member
1 Post |
Posted - 2005-06-29 : 17:28:55
|
| Thanks for the responses, though neither solves the problem I posed, which is to change the database name without (a) resorting to dynamic SQL or (b) having to edit the database name in all of my stored procedures. I already know about those options, but am hoping for a more elegant solution.Regarding the recommendation from "robvolk" that we use a different DB server for testing and development, we already do this. However, I still want to be able to use different database names for a number of reasons. First, there are times when we have several development projects going on at the same time, each of which requires its own version of the database. Second, we have a protocol here that we don't assign the production database name to our test and development databases as a safeguard--otherwise it's too easy to accidentally connect to the production DB when you want to use a test DB or vice-versa. I know about the work-arounds, but I'm looking for a layer of abstraction here. I repeat my question: Is there an elegant way to parameterize the database name in a query, so that I can switch databases by just reassigning a variable? |
|
|
|
Thrasymachus
Constraint Violating Yak Guru
483 Posts |
Posted - 2005-06-29 : 18:43:58
|
| So to boil this down you want to specify a variable value without specifying a variable. I guess you could always specify a database in your connection string, have the 2 connection strings vary between your production and testing servers and remove all database names in your stored procedure.Doing this may slightly impair performance and it will if your shop begans it's procedure with a sp_ prefix because master will be checked first.Sean RoussyPlease backup all of your databases including master, msdb and model on a regular basis. I am tired of telling people they are screwed. The job you save may be your own.I am available for consulting work. Just email me though the forum. |
|
|
|
jhermiz
3564 Posts |
Posted - 2005-06-29 : 19:01:18
|
quote:
Originally posted by Thrasymachus
So to boil this down you want to specify a variable value without specifying a variable. I guess you could always specify a database in your connection string, have the 2 connection strings vary between your production and testing servers and remove all database names in your stored procedure.Doing this may slightly impair performance and it will if your shop begans it's procedure with a sp_ prefix because master will be checked first.Sean RoussyPlease backup all of your databases including master, msdb and model on a regular basis. I am tired of telling people they are screwed. The job you save may be your own.I am available for consulting work. Just email me though the forum.
What happens when someone needs to reuse a stored procedure from another database ?I would agree with rob, and if you cant distinguish between prod and dev on two seperate boxes...i dont know what to tell ya!
Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]Imperfection living for perfection -- [url]http://jhermiz.blogspot.com/[/url] |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-06-29 : 19:06:53
|
quote:
Originally posted by reggiedba
I repeat my question: Is there an elegant way to parameterize the database name in a query, so that I can switch databases by just reassigning a variable?
No. The best way to do this would be to combine your databases into one. I'm not saying to combine test and production into one. But rather combine your Patient and Service databases into one. Then all of your stored procedures wouldn't need to refer to a database name. Then your application would handle which database environment to connect to via the connection string.Tara |
|
|
|
|
|
|
|
|
Parameterizing DB Name in a Query