Halt execution of TSQL script

How can I stop the execution of TSQL script through tsql script?

I have the following script designed to stop our developers accidently running our db modfication scripts against the wrong database.  I recently discovered that this only halts the running of the current batch.  What can I replace the 'return' with to stop execution altogether?

--In this example I want to stop the 'select * from sysobjects' statement from being run without removing the 'go'.

IF db_name() IN ('master','model','msdb','tempdb')
BEGIN
 RAISERROR('YOU ARE ATTEMPTING TO RUN THE DBMODS AGAINST THE WRONG DATABASE!',16,1)
 RETURN
END
gO
Select * From sysobjects
LVL 6
billy21Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

muzzy2003Commented:
You can't do this across batches. Why do you want to keep the GO in?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
billy21Author Commented:
Because  your dbmods file is about 100 thousand lines long and if we didn't use batches the entire script would fail as a result of 1 error.
0
muzzy2003Commented:
But you want (a) not to halt the whole script because of 1 error, and (b) to halt the whole script because of 1 error. These are obviously not compatible. You need to rethink your approach. How about not letting the developers run the script?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

billy21Author Commented:
>But you want (a) not to halt the whole script because of 1 error, and (b) to halt the whole script because of 1 error.

NO.
I want a) not to halt the whole script because of 1 error, and b) to stop the user running the script against the wrong database.
0
muzzy2003Commented:
OK. Then the only solution I can see is to include your database check at the top of every batch in the script. Wrap each batch in this:

IF db_name() IN ('master','model','msdb','tempdb')
BEGIN
  RAISERROR('YOU ARE ATTEMPTING TO RUN THE DBMODS AGAINST THE WRONG DATABASE!',16,1)
END
ELSE
BEGIN

  ... do your stuff

END
GO

0
billy21Author Commented:
not an option.
0
muzzy2003Commented:
Why? Sorry, I'm not trying to be awkward, I'm trying to understand the requirements fully.
0
billy21Author Commented:
Because the effort and contintued maintenance of doing that outweights the benefit by about 1000 to 1.  I'd prefer to do nothing than to take that course of action.
0
muzzy2003Commented:
How about creating a dummy database called "MoronDeveloper" or something, and at the top having:

IF db_name() IN ('master','model','msdb','tempdb')
BEGIN
0
muzzy2003Commented:
Sorry - hit button too soon:

IF db_name() IN ('master','model','msdb','tempdb')
BEGIN
  USE MoronDeveloper
  RAISERROR('YOU ARE ATTEMPTING TO RUN THE DBMODS AGAINST THE WRONG DATABASE!',16,1)
END
GO

Doesn't stop the script running, but does protect your system databases.
0
billy21Author Commented:
Or an endless loop

I could place the execution into an endless while loop and the user will have to hit the stop button
0
billy21Author Commented:
IF db_name() IN ('master','model','msdb','tempdb')
BEGIN
      While 1 = 1
      Begin
            RAISERROR('YOU ARE ATTEMPTING TO RUN THE DBMODS AGAINST THE WRONG DATABASE! STOP EXECUTION NOW',16,1)
      End
END
Go

Select * from sysobjects
0
billy21Author Commented:
It doesn't make it totally obvious to the user that there is a problem unless the user physically clicks on the 'messages' tab though.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.