Trying to execute use database_name command using dynamic query but not switching database name

Question

I am trying to execute use Database_name command for switching on the particular database using below dynamic query. Query executed successfully but not switch the database.

declare @dbname varchar(50)='Database_name',
        @query nvarchar(200)
set @query='use '+@dbname+''
EXEC sp_executesql @query

Community · Accepted Answer · 2020-06-20 09:12:55Z

1

Actually it works ,but the context will be gone as soon query ends

    declare @dbname varchar(50)='model',
            @query nvarchar(200)
    set @query='use '+@dbname+'
select db_name()'
    print @query
    exec (@Query)

The above query prints model as database name and context is reverted back..

More details on this here:Switching between databases with dynamic SQL

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.

one more way is to change SSMS to results to text option,copy that and execute that

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Aug 17, 2017 at 10:22

TheGameiswar

29k9 gold badges67 silver badges106 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Trying to execute use database_name command using dynamic query but not switching database name

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related