I am not sure why we cannot pass variables to USE keyword, e.g. USE @dbname. I have tried using EXEC sp_executesql @dbexec, where @dbname contains the value 'USE ' + @dbname, however, it will not really switch to the new database.
declare @dbname nvarchar(30)
declare @dbexec nvarchar(30)
set @dbname = 'new_test_db'
set @dbexec = 'USE ' + @dbname
exec sp_executesql @dbexec
Now that it is executed, it will of course change back to Master (this also the case if we use EXEC (@dbname). I would like it to remain in the new database that I have chosen, e.g. new_test_db so that I can manipulate more data inside there as per the USE keyword.
How can I really accomplish this in SQL Server 2000 Query Analyzer please. I would like to do this as we have lots of databases that ends with a numbers, so that I can perform a loop.
I thank you in advance for your help.