CODEWITHSUNDEEP
sql-server
Jha.prashant
Jha.prashant

Reputation: 263

Switching database context at run time

I am using below code to switch db context to master and create procedure and setup start up script.

BEGIN TRY
DECLARE @dbName NVARCHAR(100)
SET @dbName = DB_NAME()
USE MASTER
IF NOT EXISTS (
            SELECT name
            FROM sys.objects
            WHERE object_id = OBJECT_ID('spSetTrustWorthyOn')
            )
        EXEC (
                'CREATE PROCEDURE spSetTrustWorthyOn
        AS
        BEGIN
            ALTER DATABASE [' + @dbName + '] SET TRUSTWORTHY ON
        END'
                )

    EXECUTE sp_procoption 'spSetTrustWorthyOn'
        ,'startup'
        ,'ON'
    END TRY
    BEGIN CATCH
    END CATCH
    GO

Now Issue is when I want to switch back to existing database.I could not find any way to go back to my original database.

I also can not hard code the database as this is dynamic query and we have multiple databases.

Any help will be much appreciated.

Thanks

Upvotes: 1

Views: 634

Answers (1)

Dan Guzman
Dan Guzman

Reputation: 46203

Instead of a USE statement for the master database, qualify the catalog views and use EXEC sp_executesql statement with the master database qualified. This will avoid changing the database context in the outer script.

DECLARE
     @dbName sysname = DB_NAME()
    ,@sql nvarchar(MAX);

BEGIN TRY

    IF NOT EXISTS (
        SELECT *
        FROM master.sys.objects
        WHERE object_id = OBJECT_ID(N'spSetTrustWorthyOn')
        )
    BEGIN
        SET @sql = N'CREATE PROCEDURE spSetTrustWorthyOn
            AS
            BEGIN
                ALTER DATABASE ' + QUOTENAME(@dbName) + ' SET TRUSTWORTHY ON;
            END;';
        EXECUTE master..sp_executesql @sql;
        EXECUTE sp_procoption
             'spSetTrustWorthyOn'
            ,'startup'
            ,'ON';
    END;
END TRY
BEGIN CATCH
    THROW;
END CATCH;
GO

Upvotes: 1

Related Questions