USE <database> With sp_executesql

[grnzbra]

I have five scripts that must be run against each of three database. At the top of each script are three USE statements - one for each database. Two of the three are commented out. There is a potential problem of ensuring that these all have the same database "uncommented". Is there any way to set a field in a table to hold the name of the desired database and then use this to load a variable in USE statement
As in:

USE @DBName
GO

I've tried loading the database name into a holding table and then running

DECLARE @DBName NVARCHAR(100)
DECLARE @SQLStr NVARCHAR(4000)
SELECT @DBName = fldDBName FROM dbo.tblHoldTable
SET @SQLStr = 'USE ' + @DBName
EXEC sp_executesql @SQLStr

While this results in a response of

Commands completed successfully.

the database setting doesn't change.

 

[bborissov]

You can't use database that way.
This query is executed in separate batch and it changes database context in that batch.
But you can build the whole query and execute it with sp_executesql

DECLARE @DBName NVARCHAR(100)
DECLARE @SQLStr NVARCHAR(max)
SELECT @DBName = fldDBName FROM dbo.tblHoldTable
SET @SQLStr = 'USE ' + @DBName+';
SELECT * FROM ......
'
EXEC sp_executesql @SQLStr

or change query to use full name of the table (database schema table)

DECLARE @DBName NVARCHAR(100)
DECLARE @SQLStr NVARCHAR(max)
SELECT @DBName = fldDBName FROM dbo.tblHoldTable
SET @SQLStr = 'SELECT * FROM '+@Database+'.schemaname.TableName WHERE ....'
EXEC sp_executesql @SQLStr

Borislav Borissov
VFP9 SP2, SQL Server