Description

I have to select data from 2 databases on two different database servers. The database, tables, and fields are identical. The select statement is shown below. The problem is that I have to supply 2 parameters to this query to locate the identifier field on each database.
Unfortunately in production I will have 5 SQL databases that are all the same but located around the world.

This means (If I don't resolve this problem) I will have to supply 5 parameters that will contain exactly the same value.

For example if the identifier is 'xxx' I will need 5 parameters (because of the 5 question marks) and will have to enter 'xxx' into the parameters 5 times.

I am wondering if there is a way around this using some sort of named variable.

There will be 5 datasources forcing me to have 5 question marks to bind the parameter (identifier) to the SQL.
I have to use passthrough because I will eventually call a function within the DB but I believe it won't matter even if I don't do a passthrough. There will always be multiple ?'s to bind the indentifier value that I have to pass into the query.


SELECT identifier FROM bmddevsp05:TEXT={{ SELECT identifier as identifier FROM
Transaction_log where identifier=?}}(?) union SELECT
identifier FROM bmddevsp06:TEXT={{ SELECT identifier as identifier FROM
Transaction_log where identifier=?}}(?)

Solution
use : param as the identifier as in the following example.


SELECT id,identifier,hostname,revision_id,
DATEADD(hour,0,transaction_date) from
bmddevsp05:Transaction_log where
DATEADD(hour,0,transaction_date)
>= DATEADD(hour,0, :start_date) and
DATEADD(hour,0,transaction_date)
< DATEADD(hour,0, end_date) and
identifier=:item union SELECT
id,identifier,hostname,revision_id,
DATEADD(hour,0,transaction_date) from
bmddevsp06:Transaction_log where
DATEADD(hour,0,transaction_date)
>= DATEADD(hour,0, :start_date) and
DATEADD(hour,0,transaction_date)
< DATEADD(hour,0, end_date) and
identifier=:item