Coding a conditional "USE" statement in TSQL

[CarolCook]

Hi:
Is there any way to prompt the user for a value and generate a USE statement based on what they enter? I have a VB app that takes a product number from the user and based on a part of the product number needs to make a choice of two different databases from where to pull data.

So if the product number begins with 1, run the select statement from the database called FOO1 but if it begins with 2,run it from the database called FOO2.

Basically, trying to avoid writing two routines with two different connection strings with different initial catalogs.
Thanks

 

[RiverGuy]

No, just fully qualify the database name:

SELECT * FROM FOO1.dbo.Table1
SELECT * FROM FOO2.dbo.Table2

 

[CarolCook]

How would I write a statement that conditions between the two statements - something like:

user inputs project, then:

select * from
case when left(project,1)= 1 then foo1.dbo.table1 else
foo2.dbo.table2

SQL doesn't like this syntax though.

 

[RiverGuy]

My preference would be to allow the VB app to set the table name.

My second preference would be to create a stored procedure in SQL to conditionally query a specific table. For example:

IF @Project = 1
BEGIN
  SELECT * FROM foo1.dbo.table1
END
ELSE
BEGIN
  SELECT * FROM foo2.dbo.table2
END

My last preference would be to create a stored procedure in SQL using dynamic SQL.

 

[Crowley16]

have you thought of creating a view to pull both tables together?

create view SomeView as
select 'Proj1' as Project, * from foo1.dbo.table1
union select 'Proj2' as Project, * from foo2.dbo.table2
go

obviously requires both tables to be identical, and would be a bit cumbersome if you have to do it for a lot of tables, but just for 2 identical tables it could make things simpler...

--------------------
Procrastinate Now!

 

[CarolCook]

Yes - have tried a union but because the data only exists in one table, it fails. Have moved on to trying to create an SP with a temp table that does the logic of picking the value from the right table, populates the table and then returns the values in the table. If you have any ideas on syntax for that, let me know.

 

[Crowley16]

oh, that thread was yours as well? I posted something on it already...

just out of curiosity, why does the union fail if data only exists in 1 table?
The union should pull together all data in both tables, so as long as in either table, the relevant record(s) should be retrieved.

--------------------
Procrastinate Now!

 

[CarolCook]

Sorry - forgot to mention that the output is XML.