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', 1RECONFIGURE WITH OVERRIDEUSE MASTERGOCREATE FUNCTION ...GOEXEC SP_CONFIGURE 'ALLOW UPDATES', 0RECONFIGURE WITH OVERRIDEGO -- Also, I'd like to restore the active DB -- to that prior to the above scriptUSE @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 batch2. Restore the active DB to that prior to start of scriptBest Regards,Jim |
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2004-05-26 : 20:30:02
|
From books online.....quote: RETURNExits 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" |
|
|
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 intSET @sExists = 1IF @sExists = 1 BEGIN PRINT 'Function Exists' RETURN ENDGOPRINT 'Function Does NOT Exist'
results in:Function ExistsFunction Does NOT ExistBest Regards,Jim |
|
|
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 TRANWRAPBEGIN TRANSACTION CREATEFUNCTIONDECLARE @sExists intSET @sExists = 1IF @sExists = 1 BEGIN PRINT 'Function Exists' ROLLBACK TRANSACTION TRANWRAP RETURN ENDCOMMIT TRANSACTION CREATEFUNCTIONPRINT 'Function Does NOT Exist'COMMIT TRANSACTION TRANWRAPMeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
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 |
|
|
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 |
|
|
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.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
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 |
|
|
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> |
|
|
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 ENDGOif (SELECT error FROM #error) = 1RETURNPRINT 'Function Does NOT Exist'GOMeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
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 |
|
|
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 RETURNThen you don't have to insert into or select from #error |
|
|
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 |
|
|
Kristen
Test
22859 Posts |
Posted - 2004-06-07 : 01:03:19
|
I had an additional thoughtBasically 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 |
|
|
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.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|