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

1

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

2

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.]

1 Like
3

Thank you ScottPletcher

4

hello Mel

another way would be
to generate script using script

select 'use '+name + 'go' + ' your accounting query here '
from sys.databases
where name in () -- here you could filter your database's

this will generate something like this

use abc go your query
use def go your query
use xyz go your query

or if you want you can use
sp_msforeachdb

1 Like
5

Thank you all