Abort SQL script?

[balor]

Hi,

how do I abort an SQL-script when it contains the GO command?

I want to be able to send the customer a script that creates tables, views and storred procedures etc. and be able to abort the script. At the start of the script I check some stuff and if it's not right I dont want to run the script. I have tried to abort it in many ways but it just keep on going after the next GO.

Example:

IF(EXISTS (SELECT PR_ID FROM PR_Project WHERE PR_Dirty = 1))
BEGIN
--Here I want to abort
END

CREATE PROCEDURE dbo.usp_Test
AS
SELECT Test FROM dbo.Test

GO

CREATE TABLE dbo.TestTable (test int)

GO

INSERT INTO dbo.TestTable (test) VALUES (1)

 

[mrdenny]

You can use raiserror to display an error and stop the script. You can read more about raiserror in BOL.

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[balor]

No, it does not stop by using raiserror. The current batch stops but the next batch (after the next GO) will still execute.

 

[mrdenny]

use severity level 20. 19 stops the current batch, 20 will drop the connection.

From BOL:

Error messages with a severity level of 19 or higher stop the current batch. Errors messages with a severity level of 20 or higher are considered fatal errors and terminate the client connection.

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[balor]

Yes but to be able to specify a severity level higher then 18 the user has to be a member of the sysadmin role. If the user for example is the database owner, an error of level 16 will be raised:

Server: Msg 2754, Level 16, State 1, Line 30
Error severity levels greater than 18 can only be specified by members of the sysadmin role, using the WITH LOG option.

and the script will keep on running.

This is not a good solution for me because the database owners (not just the sysadmin) have to be able to run the scripts.

 

[ESquared]

Have you tried removing the GOs? They are only needed for things like creating stored procedures. In the example you gave, you could use a goto.

IF ErrorCondition = True GOTO SkipToEnd

{Other SQL Statements}

SkipToEnd:

Final SQL Statement or End of Procedure

You could also use RETURN if it's actually a stored procedure (or function), which immediately exits with a return value, without executing any more code.

-------------------------------------
There are 10 kinds of people in the world: those who know binary, and those who don't.

 

[balor]

No, GOTO does not work. The label SkipToEnd: should not be visible for the GOTO statement because it's in another batch (because it's on the other side of a GO command).

RETURN only works in a procedure, not in a script.

 

[ESquared]

Um... I said, have you tried removing the GOs?

I also said "if it's actually a stored procedure."

Why do you need all these things to run in separate batches?

Can you use a DTS package that aborts on failure of any step?

-------------------------------------
There are 10 kinds of people in the world: those who know binary, and those who don't.

 

[mrdenny]

The only other thing that I can recommend is to within the script require that the user be sysadmin to run. It seams that this is a software package update script or software package install script of some sort. Requiring that it be run with sysadmin access is not out of the ordinary.

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[ESquared]

Oops {blush} you DO have a create procedure thing in there. I apologize.

-------------------------------------
There are 10 kinds of people in the world: those who know binary, and those who don't.