Stoping Batch Execution by Conditional

[nacrotek]

I have created a batch script that creates stored procedures, alters and creates tables, etc. For a security measure I want to build in a check at the beginning of the batch file to ensure that the batch will only be run against the correct database.

Here is what I have tried:

declare @deployToDB varchar(100),
@currentDB varchar(100)
select @deployToDB = 'someDB',
@currentDB = db_name()

if @currentDB <> @deployDB
begin
return
end

<rest of batch file here>


The problem is that even if the @currentDB and @deployDB vars are different and the return statement is reached, the rest of the batch is executed.

I can't wrap the rest of the batch in an else block because of GO commands that exist.

I need a way to end all execution when the DB name check fails.

 

[NoCoolHandle]

Go's define a batch, you seem to be talking about a script.

no..

for instance

select 'a'
select 'b'
return
select 'C'
select 'D'
go
select 'E'
select 'f'
raiserror ('boom',16,16)
select 'g'
select 'h'
go
select 'i'

returns
a
b
e
f
BOOM
and I

So about the only thing you can do is break you scripts up and return errorcodes.

HTH


Rob

 

[micha123]

The return commands only exists the current batch. That means that if you have go commands, your program will go there (to the next batch - each batch is ended by "go").
The best way to do what you need is with a temporary table that holds a flag field. This field tells each batch if it has to run or not:

create table #Flag (fl bit)
insert into #Flag values (0) -- OK
declare     @deployToDB varchar(100),
            @currentDB varchar(100)
select @deployToDB = 'someDB',
       @currentDB = db_name()

if @currentDB <> @deployDB
begin
    update #Flag set fl = 1
    return
end
......
go
if exists (select 1 from #Flag where fl = 1)
    return
....
go
if exists (select 1 from #Flag where fl = 1)
    return
....

"A long life is when each day is full and every hour wide!"
(A Jewish Mentor)
M.

 

[Catadmin]

You could try a WHILE loop which will check the code before it runs the statements. You'd need to come up with a Counter though, and make sure you increment or decrement it before the END of the loop.



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[NoCoolHandle]

Not possible accross batches cata