using diffenet db one at a time

[OldSlowDog]

I am working on a project that I have to select the same table from each store and say, print a report with the query results. I am trying to use just one Stored Procedure by passing differnet city name to it.

The problem is that I can't swtich from db to db. I tried DECLARE @City in different ways. they all failed. Please help.

I tried and failed:
DECLARE @City
SET @City = 'Phoenix'
SELECT * FROM @City.dbo.SalesTbl

I also tried and failed:
DECLARE @City
SET @City = 'Phoenix'
USE @City
SELECT * FROM SalesTbl

The SQL Server setup:
XYZEnterprise(SQL Server 9.0.4035)
DataBases
Phoenix
Dallas
SaltLake
SanAntonio


Thanks for helping

 

[bborissov]

You should use so called Dynamic SQL:

DECLARE @City varchar(200)

SET @City = 'Phoenix'
DECLARE @sql varchar(max)
SET @sql = 'SELECT * FROM '+@City+'.dbo.SalesTbl'
EXEC (@sql)

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[SQLBill]

You would have to do dynamic SQL...

DECLARE @City VARCHAR(50)
DECLARE @SQLCmd VARCHAR(100)
SET @City = 'Phoenix'

SET @SQLCmd = 'SELECT * FROM ' + @City + '.dbo.SalesTbl'

PRINT @SQLCmd
EXEC(@SQLCmd)

You need to remember to set your data type when you declare a variable. When you run this for the first time, comment out the EXEC line and you will see what is being run.

Enhancement, you could create a table with all of the city names and then loop through that table to put each city name into the variable.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[SQLBill]

Dang....Borislav beat me by moments.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[OldSlowDog]

Two of you are great! I like the suggested enhancement by looping through the city table.
Sincerely thank you all. Have a great day!

 

[TheBugSlayer]

Dynamic SQL is the best way to go...Otherwise the more verbose

CREATE PROCEDURE dbo.ManyDatabases @CityDB NVARCHAR(128)
AS
BEGIN
    IF @CityDB = 'DB1' BEGIN
       ServerName.DB1.SchemaName.TableName
       ...
    END
    ELSE IF @CityDB = 'DB2' BEGIN
       ServerName.DB2.SchemaName.TableName	
       ...
    END
    ELSE IF @CityDB = 'DB3' BEGIN
       ServerName.DB3.SchemaName.TableName
       ...
    END
    ELSE BEGIN
       ServerName.DB1.SchemaName.TableName
       ...
    END
END

should also work if all DBs are on the same server...but you will have to modify the code to add an IF statement each time you create a new city DB...

Good luck.

MCITP SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)

 

[markros]

Take a look at this blog post explaining such idea in details with various code samples for many scenarios:
How to get information about databases without a loop

PluralSight Learning Library