Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 How to Abort Entire Script?

Author  Topic 

Apollois
Starting Member

49 Posts

Posted - 2004-05-26 : 20:07:48
How do you handle conditional processing that spans across batches in SS2K SP3a Query Analyzer?

For example, in psuedo code:

==================================================
IF Function Exists
BEGIN
Print message
Abort the entire script <==== HOW DO I DO THIS??
END

-- FLOW SHOULD BE HERE ONLY
-- IF FUNCTION DOES NOT EXIST --

EXEC SP_CONFIGURE 'ALLOW UPDATES', 1
RECONFIGURE WITH OVERRIDE
USE MASTER
GO

CREATE FUNCTION ...
GO

EXEC SP_CONFIGURE 'ALLOW UPDATES', 0
RECONFIGURE WITH OVERRIDE
GO

-- Also, I'd like to restore the active DB
-- to that prior to the above script

USE @OldDB -- won't work because variables
-- are all LOCAL to a batch.

=======================================================

Any ideas on how to:
1. Abort the entire script, not just the batch
2. Restore the active DB to that prior to start of script


Best Regards,
Jim

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2004-05-26 : 20:30:02
From books online.....

quote:

RETURN
Exits unconditionally from a query or procedure. RETURN is immediate and complete and can be used at any point to exit from a procedure, batch, or statement block. Statements following RETURN are not executed.



DavidM

"Always pre-heat the oven"
Go to Top of Page

Apollois
Starting Member

49 Posts

Posted - 2004-05-26 : 20:36:30
David,

Thanks for the suggestion, but RETURN only aborts/exits the current batch. I have already tested this:


DECLARE @sExists int

SET @sExists = 1

IF @sExists = 1
BEGIN
PRINT 'Function Exists'
RETURN
END

GO

PRINT 'Function Does NOT Exist'


results in:

Function Exists
Function Does NOT Exist



Best Regards,
Jim
Go to Top of Page

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-05-26 : 23:04:38
GO is a batch terminator. Everything before the batch is one set. Everything after is another. The two are in no way relate.....ever!!!! You can't use the GO in a stored procedure anyway. If you wish, you can do this:

BEGIN TRANSACTION TRANWRAP

BEGIN TRANSACTION CREATEFUNCTION

DECLARE @sExists int

SET @sExists = 1

IF @sExists = 1
BEGIN
PRINT 'Function Exists'
ROLLBACK TRANSACTION TRANWRAP
RETURN
END

COMMIT TRANSACTION CREATEFUNCTION

PRINT 'Function Does NOT Exist'

COMMIT TRANSACTION TRANWRAP

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2004-05-27 : 14:03:10
quote:
Originally posted by derrickleggett


Is there a way to abort a SQL Script (i.e. a bunch of statememts being run in Query Analyser) that has loads of GOs in it?

We have UPGRADE scripts that do Alter Table / Insert Data / etc. type stuff. Each block has a BEGIN TRANS / COMMIT surround, but I'd really like to have a way to ABORT in the middle if I found some duff condition. Anything committed up to that point wouldbe OK (I can manually / safely resume from the next "Block"), but I'd need to ROLLBACK the current block and NOT execute any subsequent blocks.

Currently we highlight each block, manually, down to [but not including] the next COMMIT statement; run that and then run the COMMIT and then next section, but its a bit painful!

A GOTO would be OK by me <bg>

Kristen
Go to Top of Page

Apollois
Starting Member

49 Posts

Posted - 2004-05-27 : 15:21:27
Kristen,

>>Is there a way to abort a SQL Script<<

That is exactly my question.

Hopefully someone will be able to answer this.


Best Regards,
Jim
Go to Top of Page

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-05-27 : 19:05:19
Well, you could set a variable to 1 for example and in each batch test for the variable. If it's one abort. That would effectively abort the entire transaction. If you changed the number on each batch by one and just looked for greater than 0 you could see where it broke also.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.
Go to Top of Page

Apollois
Starting Member

49 Posts

Posted - 2004-05-27 : 19:20:12
derrick,
Variables don't persist across batches, so this won't work.
As soon as QA hits a GO, everything is cleared.


Best Regards,
Jim
Go to Top of Page

MichaelP
Jedi Yak

2489 Posts

Posted - 2004-05-27 : 19:27:12
Don't make is separate batches then. Do you really need the "GO" statements in there??

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>
Go to Top of Page

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-05-27 : 19:28:23
That's true and was a stupid mistake. (been making a lot of those lately)

Here's a possibility. Can't say I like it though.

CREATE TABLE #error(error INT)

INSERT #error(error) VALUES(0)

IF (SELECT error FROM #error) = 1
BEGIN
PRINT 'Function Exists'
RETURN
END
GO

if (SELECT error FROM #error) = 1
RETURN
PRINT 'Function Does NOT Exist'

GO

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.
Go to Top of Page

Apollois
Starting Member

49 Posts

Posted - 2004-05-27 : 19:43:27
derrick,

Although normally we try to avoid temp tables, in this case it's not a production script that gets run hundreds of times a day. Actually, it will probably be well under 10 times a day.

So performance is NOT an issue for my specific need. Your suggestion of a temp table should work fine.

Thanks.


Best Regards,
Jim
Go to Top of Page

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-05-27 : 20:14:03
A variation on Derriks solution

IF Object_ID('tempdb..#error') IS NOT NULL
RETURN

Then you don't have to insert into or select from #error

Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2004-05-28 : 02:49:09
quote:
Originally posted by derrickleggett

That's true and was a stupid mistake. (been making a lot of those lately)

CREATE TABLE #error(error INT)


The second answer was well worth waiting for though ...

Thanks,

Kristen
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2004-06-07 : 01:03:19
I had an additional thought

Basically what I'm trying to do is to run long scripts of "CREATE this" and "ALTER that" to bring a database up to latest schema etc. when rolling out a new version.

My script is littered with GO's (and "-- ========= STEP 01 - Run to there ========== --" that I use to highlight & run sections of the script "Just in case".

Anyways ...

Strikes me that I want is to run each part of the script down to the next GO and then ABORT if there was an error, then carry on down to the next GO.

So I don't really want to be using QA, I want a tiny little application that reads text from a file up to next line starting with GO, executes that, checks for errors, and does rollback and abort if any found. And if it aborts with error THEN I'll run the script in QA to see what went wrong.

Perhaps such a tiny-application exists to save me the bother of writing one?

Kristen
Go to Top of Page

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-06-07 : 08:09:40
Perhaps you should just be using osql and checking the output. You could use a script that runs in QA to run the entire batch of osql scripts and return any errors. All you have to do on any given error is open up the individual script and run it. The batch would still continue though. Just a thought.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.
Go to Top of Page
   

- Advertisement -