Switching between databases with dynamic SQL

Question

I have a process which involves executing various commands between multiple databases - however, when I use dynamic SQL to change DB with 'use @var', then it doesn't actually change the database.

Executing this in [test_db]:

declare @currentDB varchar(max)
declare @sql varchar(max)

set @currentDB =  DB_NAME()
set @sql = 'use  [' + @currentDB +']'

use master

exec(@sql)

select  DB_NAME()

Returns [Master] as the current database name - if I put use [test_db] as a command, rather than dynamically, then it returns the correct name.

Is there a way to do this which will correctly switch between databases?

Answer 1

Session-level changes made in a sub-process (i.e. EXEC / sp_executesql) go away when that sub-process ends. This covers USE and SET statements as well as any local temporary tables created in that sub-process. Creation of global temporary tables will survive the sub-process, and so will modifications made to local temporary tables that exist prior to the sub-process starting, and any changes to CONTEXT_INFO (I believe).

So no, you cannot dynamically change the current database. If you need to do something like this, you will need to execute any subsequent statements that rely upon the new database context also within that Dynamic SQL.

Answer 2

Sure, there is a way - there's always a way...

If you declare variable and store in it the database and the procedure to run, you can exec it, with parameters.

Example

use tempdb;

select db_name();

declare @db sysname = 'master.sys.sp_executesql';

exec @db N'select db_name()';

set @db = 'msdb.sys.sp_executesql';

exec @db N'select db_name()';

It is trivial to then pass a query with parameters to be run in any database

declare @proc sysname, @sql nvarchar(max), @params nvarchar(max);

select 
  @proc = 'ssc.sys.sp_executesql'
, @sql = N'select top 10 name from sys.tables where name like @table order by name;'
, @params = N'@table sysname';

exec @proc @sql, @params, @table = 'Tally%'

I know this doesn't change database context in the main query, but wanted to demonstrate how you can conveniently work in another database in a safe parameterised way without too much bother.

Answer 3

Basing this on @Mister Magoo's answer...

CREATE PROCEDURE dbo.Infrastructure_ExecuteSQL
(
    @sql NVARCHAR(MAX),
    @dbname NVARCHAR(MAX) = NULL
)
AS BEGIN
    /*
        PURPOSE
            Runs SQL statements in this database or another database.
            You can use parameters.

        TEST
            EXEC dbo.Infrastructure_ExecuteSQL 'SELECT @@version, db_name();', 'master';

        REVISION HISTORY
            20180803 DKD
                Created
    */

    /* For testing.
    DECLARE @sql NVARCHAR(MAX) = 'SELECT @@version, db_name();';
    DECLARE @dbname NVARCHAR(MAX) = 'msdb';
    --*/

    DECLARE @proc NVARCHAR(MAX) = 'sys.sp_executeSQL';
    IF (@dbname IS NOT NULL) SET @proc = @dbname + '.' + @proc;

    EXEC @proc @sql;

END;

I have lots of maintenance-related uses for this.

Answer 4

This works too.

declare @Sql nvarchar(max),@DatabaseName varchar(128)
set @DatabaseName = 'TestDB'

set @Sql = N'
    declare @Sql nvarchar(max) = ''use ''+@DatabaseName
    set @Sql = @Sql +''
    select db_name()
    ''
exec (@Sql)
'
exec sp_executesql @Sql,N'@DatabaseName varchar(128)',@DatabaseName

Answer 5

Learning from the previous post I went a little deeper and impressed myself...

DECLARE @Debug              BIT = 1
DECLARE @NameOfDb           NVARCHAR(200)   = DB_NAME()
DECLARE @tsql               NVARCHAR(4000)  = ''

    IF OBJECT_ID('Tempdb.dbo.#tbl001') IS NOT NULL DROP TABLE #tbl001
        CREATE TABLE #tbl001(
            NameOfDb      VARCHAR(111))
    INSERT INTO #tbl001(NameOfDb)
        VALUES('db1'),('db2'),('db3'),('db4')
SET @tsql = N'
DECLARE @sql nvarchar(max) 
set @sql = N''
;WITH a AS (
    SELECT NumOf = COUNT(*),
        c.Field1,
        c.Field2,
        c.Field3
    FROM ''+@NameOfDb2+''.dbo.TBLname c
    WHERE Field3 = ''''TOP SECRET''''
    GROUP BY
        c.Field1,
        c.Field2,
        c.Field3
    HAVING COUNT(*)>1
)
SELECT a.NumOf, c.* 
FROM ''+@NameOfDb2+''.dbo.TBLname c
JOIN a ON c.Field1=a.Field1 AND c.Field2=a.Field2 AND c.Field3=a.Field3''
exec (@sql)
'
DECLARE SmplCrsr CURSOR STATIC LOCAL FORWARD_ONLY READ_ONLY FOR 
    SELECT * FROM #tbl001

OPEN SmplCrsr;
FETCH NEXT FROM SmplCrsr
    INTO @NameOfDb

WHILE @@Fetch_Status=0
    BEGIN
        IF (@Debug = 1) 
            BEGIN
                EXEC sys.sp_executesql @tsql,N'@NameOfDb2 varchar(111)',@NameOfDb
            END
        ELSE 
            BEGIN
                PRINT @tsql + '--   DEBUG OFF'
            END
        FETCH NEXT FROM SmplCrsr
            INTO @NameOfDb
    END
CLOSE SmplCrsr;
DEALLOCATE SmplCrsr;