billy21
asked on
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','
BEGIN
RAISERROR('YOU ARE ATTEMPTING TO RUN THE DBMODS AGAINST THE WRONG DATABASE!',16,1)
RETURN
END
gO
Select * From sysobjects
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','
BEGIN
RAISERROR('YOU ARE ATTEMPTING TO RUN THE DBMODS AGAINST THE WRONG DATABASE!',16,1)
RETURN
END
gO
Select * From sysobjects
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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?
ASKER
>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.
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.
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','
BEGIN
RAISERROR('YOU ARE ATTEMPTING TO RUN THE DBMODS AGAINST THE WRONG DATABASE!',16,1)
END
ELSE
BEGIN
... do your stuff
END
GO
IF db_name() IN ('master','model','msdb','
BEGIN
RAISERROR('YOU ARE ATTEMPTING TO RUN THE DBMODS AGAINST THE WRONG DATABASE!',16,1)
END
ELSE
BEGIN
... do your stuff
END
GO
ASKER
not an option.
Why? Sorry, I'm not trying to be awkward, I'm trying to understand the requirements fully.
ASKER
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.
How about creating a dummy database called "MoronDeveloper" or something, and at the top having:
IF db_name() IN ('master','model','msdb','
BEGIN
IF db_name() IN ('master','model','msdb','
BEGIN
Sorry - hit button too soon:
IF db_name() IN ('master','model','msdb','
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.
IF db_name() IN ('master','model','msdb','
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.
ASKER
Or an endless loop
I could place the execution into an endless while loop and the user will have to hit the stop button
I could place the execution into an endless while loop and the user will have to hit the stop button
ASKER
IF db_name() IN ('master','model','msdb','
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
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
ASKER
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.
ASKER