SQLTeam.com | Weblogs | Forums

How can I make database name as values to a variable?


Hello SQL expert,

I have accounting query that I am using over and over for a lot of different databases. I am thinking to create a parameter and pass the database name as a value when running the same query statement but on different databases.

How can I do that?

Thank you all


By far the easiest and cleanest way to do that is to create the proc in the master db, then mark it as a "system" proc. You can then call that proc from any db and it runs in the context of that one db. The only downside to this method is that the proc name must start with "sp_".

[Obviously this method wouldn't apply if you didn't have access to create a proc in the master db.]