Link to home
Start Free TrialLog in
Avatar of jaldinger
jaldinger

asked on

Use SQL Variable in Database name

I am trying to run the same query on a series of databases, but T-SQL would not let me use the variable name as the name of the database to query. Consider the following query:

DECLARE db_cursor CURSOR
FOR SELECT name FROM sys.databases WHERE LEFT(name,4) = 'SBO_' AND RIGHT(name,4) <> 'TEST'

-- declare the variables that will hold values retrieved by the cursor
DECLARE @db_id CHAR(10)
OPEN db_cursor

FETCH NEXT FROM db_cursor INTO @db_id

-- loop through the results
WHILE @@FETCH_STATUS = 0
BEGIN
      SELECT CompnyName FROM [@db_id]..[OADM]
      --SELECT @db_id (this works fine, the above does not)
      FETCH NEXT FROM db_cursor INTO @db_id
      
END

-- close and deallocate the cursor
CLOSE db_cursor
DEALLOCATE db_cursor

Anybody have any idea on how to solve this?

Thanks in advance,

Joerg.
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

unless you use dynamic sql, there is no way to make the database name a variable
Avatar of jaldinger
jaldinger

ASKER

Thank you. I have never heard of Dynamic SQL. So what would the script look like in Dynamic SQL that does what I'm looking for?
before we go into that issue, 2 questions:
* are you SURE that you need a cursor? ie, what is the code about?
* are you SURE that you need to put the dbname as variable? couldn't you connect to the correct database in the first place?
angellll:
What I need to do is execute the same SELECT statement against a varying number of databases on the server. I can do that from code by first getting the database names (SELECT FROM sys.databases) and then running the SELECT query once against each of them. I was looking for a way to get the complete result set with a single query to the server because bandwidth and latency in this application are an issue.
ASKER CERTIFIED SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
That hasn't completely solved my problem, but I consider it good enough. Thanks.