'Use <database> ' statement not executing T-SQL

Question

Okay, this seems funny for a simple script: but I really need this small piece to execute well.

DECLARE @dbName NVARCHAR(50)
SET @dbName = 'rx_lab'
DECLARE @SQL02 NVARCHAR(MAX)
SET @SQL02 = 'USE ['+ convert(nvarchar(50),@dbName) +']'
exec sp_executesql @SQL02
SELECT DB_NAME()

This still returns the 'master' database (instead of 'Rx_lab') I have even tried using

exec ('use '+@dbName)

No change. It didn't work even without the conversion to nvarchar. However, when I use 'USE [Rx_lab]' directly it returns well. This is part of stored procedure where the @dbName is main parameter.

Answer 1

sp_executesql creates a new execution context which ceases to exist when the executed statements return. The USE only has effect within the sp_executesql. Try this

SET @SQL02 = 'USE ['+ convert(nvarchar(50),@dbName) +']; SELECT DB_NAME();'

and you'll get what you're after.

• Chagbert: @Michael_Green thanks buddy. However I would still need to exec the assignment from your answer. Yep, this will give me the DB_NAME() in the executed context but statements AFTER this will Not be using the new db assignment - which is what I want...
• Chagbert: on second thought, if I qualified each statement with a 'USE' statement then indeed it will execute in that db context. Thanks. Example: SET --SQL02 = 'USE ['+ convert(nvarchar(50),@dbName) +'] IF EXISTS (SELECT * FROM sys.database_principals WHERE name = N''rxpipewami'') DROP USER [rxpipewami] '
• Michael Green: You have it right. The USE statement only has effect within the SQL submitted to sp_executesql. As soon as sp_executesql returns the USE is no longer in effect.

Answer 2

but statements AFTER this will Not be using the new db assignment - which is what I want

You can use 3 part naming dbname.schema.objectName. Below is an example to get you started. You can modify as per your needs :

SET NOCOUNT ON
DECLARE @dbname VARCHAR(max)
 ,@SQLText VARCHAR(8000)
SELECT @dbname = min(NAME)
FROM master..sysdatabases
WHERE STATUS NOT IN (
 128
 ,1024
 ,6292500
 )
 AND STATUS & 1024 <> 1024
 AND STATUS & 128 <> 128
 AND STATUS & 512 <> 512
 AND STATUS & 32 <> 32
 AND dbid > 4
WHILE @dbname IS NOT NULL
BEGIN
 --PRINT @dbname
 SELECT @SQLText = '
 select ' + ''''+quotename(@dbname)+'''as DBNAME,' + 'count(1) as NoOFTables from ' + quotename(@dbname) + '..sysobjects where [type] =''U'' '
 PRINT @SQLText
 EXEC (@SQLText)
 SELECT @dbname = min(NAME)
 FROM master..sysdatabases
 WHERE STATUS NOT IN (
 128
 ,1024
 ,6292500
 )
 AND STATUS & 1024 <> 1024
 AND STATUS & 128 <> 128
 AND STATUS & 512 <> 512
 AND STATUS & 32 <> 32
 AND dbid > 4
 AND NAME > @dbname
END