GOTO label after switching USE database

Question

Can anyone explain why this works:

use MyDb1
if NOT EXISTS (select * from sys.objects where name = 'MyTable' and type = 'U' )
create table MyTable(MyColumn int not null)
use MyDb2
if NOT EXISTS (select * from sys.objects where name = 'MyTable' and type = 'U' )
create table MyTable(MyColumn int not null)

But this is not so:

use MyDb1

mylabel:
if NOT EXISTS (select * from sys.objects where name = 'MyTable' and type = 'U' )
create table MyTable(MyColumn int not null)

if(DB_NAME()='MyDb1')
begin
    use MyDb2
    goto mylabel
end

The flow is correct and the second check for IF NOT EXISTS works, but when it tries to create a table, I get the following error.

The database already has an object named MyTable.

this is greatly simplified, but at the same time it will save a lot of duplicate table creations in two almost identical databases.

Answer 1

Very strange behavior. If I had guessed, I would say that this is the context of the "usage database" sitting on top of the label :. I would never use a label in T-SQL, but your call.

, , . , < < < ,

set nocount on
use tempdb
create database db1
create database db2
GO

use db1

mylabel:
print 'in-' + db_name()
if NOT EXISTS (select * from sys.objects where name = 'MyTable' and type = 'U' )
begin
    print 'create-' + db_name()
    create table MyTable(MyColumn int not null)
    -- exec ('create table MyTable(MyColumn int not null)') -- <<<
end

if(DB_NAME()='db1')
begin
    print 'switch'
    use db2
    goto mylabel
end

GO
use tempdb
drop database db1
drop database db2

:

in-db1
create-db1
switch
in-db2
create-db2
Msg 2714, Level 16, State 6, Line 9
There is already an object named 'MyTable' in the database.

Answer 2

:

MyTable (MyColumn int not null)

exec sp_executesql N'create table MyTable (MyColumn int not null) '