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.
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.
unless you use dynamic sql, there is no way to make the database name a variable
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?
* 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?
ASKER
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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
That hasn't completely solved my problem, but I consider it good enough. Thanks.