SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Cursor and USE statment with variables
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

changcg
Starting Member

USA
2 Posts

Posted - 09/18/2013 :  12:26:13  Show Profile  Reply with Quote
Hello,
I have a cursor that goes through a table with the names of all the database in my server. So for each fetch, the cursor gets the name of a database and assign it to a variable, @databaseName, and try to do some queries from that database by using the command "USE @databaseName". But "USE" doesn't take the variable @databaseName; it is expecting a database name (i.e. USE master).
Here is my code:

Declare @databaseName varchar(50)
Declare c_getDatabaseName CURSOR for SELECT name from tblDatabases
OPEN c_getDatabaseName
FETCH NEXT from c_getDatabaseName into @databaseName
While @@FETCH_STATUS = 0
BEGIN
USE @databaseName <-- here is the problem
GO
-- more code here
-- more code here

END
CLOSE c_getDatabaseName
DEALLOCATE c_getDatabaseName
GO

Any Ideas on how to get USE to take the variable value ?
Any help will be greatly appreciated. Thanks in advance.
changcg


James K
Flowing Fount of Yak Knowledge

3760 Posts

Posted - 09/18/2013 :  12:52:58  Show Profile  Reply with Quote
You may be able to make use of the undocumented sp_MSforeachdb http://www.databasejournal.com/features/mssql/article.php/3441031/SQL-Server-Undocumented-Stored-Procedures-spMSforeachtable-and-spMSforeachdb.htm

If that does not work for you, the other alternative would be to use dynamic SQL.
Go to Top of Page

changcg
Starting Member

USA
2 Posts

Posted - 09/18/2013 :  15:32:16  Show Profile  Reply with Quote
I will try spMSforeachdb, it seems to be powerfull.
Thanks a lot for your help.
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3760 Posts

Posted - 09/18/2013 :  16:27:18  Show Profile  Reply with Quote
Great. Now that I am thinking about it, dynamic SQL would not work for you anyway.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000