当前位置: 动力学知识库 > 问答 > 编程问答 >

sql server - GOTO label after USE database switch

问题描述:

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 doesn't:

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 right and the 2nd check for IF NOT EXISTS works but when it tries to create the table I'm getting the following error

There is already an object named 'MyTable' in the database.

this is greatly simplified but getting this to work will save me a lot of duplicated table creations across two nearly identical databases

网友答案:

Very strange behaviour. If I were to guess, I would say it is the context of the "use database" sitting on top of the label:. I would never use the label in T-SQL, but your call.

The output from this test shows it going through the right motions, but just failing for no reason. If you uncomment the line marked <<< and comment the one above it, it then works correctly

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

output:

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.
网友答案:

change:

create table MyTable(MyColumn int not null)

to:

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

分享给朋友:
您可能感兴趣的文章:
随机阅读: