?
Solved

Halt execution of TSQL script

Posted on 2004-11-18
13
Medium Priority
?
6,239 Views
Last Modified: 2012-06-27
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
0
Comment
Question by:billy21
  • 7
  • 6
13 Comments
 
LVL 16

Accepted Solution

by:
muzzy2003 earned 2000 total points
ID: 12616084
You can't do this across batches. Why do you want to keep the GO in?
0
 
LVL 6

Author Comment

by:billy21
ID: 12616109
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
 
LVL 16

Expert Comment

by:muzzy2003
ID: 12616183
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 6

Author Comment

by:billy21
ID: 12616210
>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
 
LVL 16

Expert Comment

by:muzzy2003
ID: 12616241
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
 
LVL 6

Author Comment

by:billy21
ID: 12616540
not an option.
0
 
LVL 16

Expert Comment

by:muzzy2003
ID: 12616615
Why? Sorry, I'm not trying to be awkward, I'm trying to understand the requirements fully.
0
 
LVL 6

Author Comment

by:billy21
ID: 12616661
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
 
LVL 16

Expert Comment

by:muzzy2003
ID: 12616684
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
 
LVL 16

Expert Comment

by:muzzy2003
ID: 12616690
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
 
LVL 6

Author Comment

by:billy21
ID: 12616698
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
 
LVL 6

Author Comment

by:billy21
ID: 12616729
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
 
LVL 6

Author Comment

by:billy21
ID: 12616742
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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question