?
Solved

Use a variable for the database name?

Posted on 2005-04-27
5
Medium Priority
?
226 Views
Last Modified: 2010-03-19
I am trying to pull the same result set from databases on mulitiple linked servers.  The databases are the same on each, so the same query will work.  I shouldn't have to duplicate this code, I know, but I how do I step through the different dbs?  I can get the db names into a cursor, but "USE @variable" doesn't seem to be allowed.  Also, a full name doesn't work with the "USE" command (USE server.db).

Can someone help me with the best approach to only write my query once?
0
Comment
Question by:tallison122065
2 Comments
 
LVL 28

Accepted Solution

by:
rafrancisco earned 500 total points
ID: 13876363
As far as I know, you cannot use a variable in the USE command, like what you're doing in "USER @Variable".  Also, you cannot use server.db in the use.

I guess dynamic SQL will work in your case.
0
 
LVL 70

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 500 total points
ID: 13877546
I think you will have to use dynamic SQL one way or another.

Probably the easiest way is to write the SQL and store it in a variable, but use "placeholders" instead of the actual server and db names.  For example, something like this:


DECLARE @sqlTemplate VARCHAR(1000)
DECLARE @sql
SET @sqlTemplate = 'SELECT *
FROM ?server.?db.dbo.tableName
WHERE ...
ORDER BY ...'

Then create cursors for the server and dbs and loop thru each in turn; do a REPLACE of the ?server and ?db with the actual server and db names.  For example:

DECLARE serverCsr CURSOR FOR
SELECT 'server1' AS server
UNION ALL
SELECT 'server2'
UNION ALL
SELECT 'server3'
...

DECLARE dbCsr CURSOR FOR
...

DECLARE @server NVARCHAR(128)
DECLARE @db NVARCHAR(128)

OPEN serverCsr

DECLARE @server_status INT

FETCH NEXT FROM serverCsr INTO @server
SET @server_status = @@FETCH_STATUS
WHILE @server_status = 0
BEGIN
    OPEN dbCsr
    FETCH NEXT FROM dbCsr INTO @db
    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @sql = REPLACE(REPLACE(@sqlTemplate, '?server', @server), '?db', @db)
        EXEC(@sql)
        FETCH NEXT FROM dbCsr INTO @db
    END --WHILE    
    FETCH NEXT FROM serverCsr INTO @server
    SET @server_status = @@FETCH_STATUS
END --WHILE

0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

839 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