?
Solved

Use SQL Variable in Database name

Posted on 2007-04-02
6
Medium Priority
?
1,859 Views
Last Modified: 2008-01-09
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.
0
Comment
Question by:jaldinger
  • 3
  • 3
6 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 18837967
unless you use dynamic sql, there is no way to make the database name a variable
0
 

Author Comment

by:jaldinger
ID: 18837986
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?
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 18838027
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?
0
Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

 

Author Comment

by:jaldinger
ID: 18838070
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.
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 750 total points
ID: 18838392
the following might help:

exec sp_msforeachdb '
 SELECT CompnyName FROM ?..[OADM]
'

0
 

Author Comment

by:jaldinger
ID: 20328795
That hasn't completely solved my problem, but I consider it good enough. Thanks.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how the fundamental information of how to create a table.

601 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question