0

Note 1: From what I have seen in my research so far, there is no way to accomplish what I'm looking to do, but I hope I'm wrong ...

Note 2: The goal of this is to eliminate the need to change connection between queries to different databases

I am writing a SQL script (in SSMS) to look up information based on an id number. There are 2 databases that this query can run against (a testing DB and a production DB). An id number will either be in the testing DB or the production, but not both. What I want to do is to use the :CONNECT statement in SQLCMD mode (or an alternative method, but I've yet to find one) to connect to the appropriate DB. Like this:

:CONNECT /*Testing database name*/
IF NOT EXISTS (SELECT * FROM /*appropriate table*/ WHERE ID = @ID)
    :CONNECT /*Production database name*/

/*Continue with the rest of the query*/

Theoretically, the code above would ensure that I am connected to the correct DB (assuming the ID exists at all). What keeps happening though is that the 2nd :CONNECT statement activates regardless of the IF EXISTS, resulting in the query always running against production.

Is there a way to accomplish this goal? Thank you in advance!

Brian
  • 81
  • 3
  • 1
    This doesn't work because `IF NOT EXISTS` is a T-SQL statement, while `:CONNECT` is a SQLCMD statement. These are unrelated: SQLCMD is executed by the SSMS parser, while T-SQL is executed by the database. Since SQLCMD has no conditional logic, you can't do what you're trying to do in an SQLCMD script. If the databases are on the same instance, use `USE` (a T-SQL command) instead of `:CONNECT`. If they're on different instances, you cannot do this from SSMS. You'll have to invoke `sqlcmd` externally with appropriate parameters. – Jeroen Mostert Apr 25 '17 at 11:13

1 Answers1

0

Since I don't know what do you need to eliminate the connection change, have you considered joining the two tables and proceed from there to the desired result? You could check the existence of the id in the returned table if that's the desired flow.

You could check this also. Let me know if it helps!

Community
  • 1
  • 1
Cosmin
  • 152
  • 3
  • 18