Transact SQL Batch files and GO

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)

Transact SQL Batch files and GO

Author

Topic

bmsgharris
Starting Member

10 Posts

Posted - 2005-10-03 : 09:39:35

I have a TSQL batch with multiple GO's in it (this is due to the nature of the batch). When the batch starts I have a test to see if some previously created objects are present in the database, and I want to exit the batch file if these objects are not present. How do I prevent the batch file reader from executing past the next GO? I would rather not have to test a bit variable, in each section.

Example:

IF [Required objects are present]
BEGIN
PRINT [Some Message]
RETURN
END

GO

<execution restarts here>

TIA

Graham Harris

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2005-10-03 : 09:44:20

why not have the code between each batch as a job step in sqlserver agent? then if the required objects aren't present you can raise an error and the job can be setup to exit reporting an error per each jobstep - that way the other stuff won't execute :)

Duane.

bmsgharris
Starting Member

10 Posts

Posted - 2005-10-03 : 09:54:43

So how do I go about doing that?

Graham Harris

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2005-10-03 : 10:04:01

In enterprise manager under the management folder of your server you'll find sql server agent.
In there you can create jobs.

make each step a transact sql step.
put each batch of code into one step.
set each step to goto next step On Success (The last step must quit job reporting success)
but for on failure the step must Quit job reporting failure.

It's a very easy tool to work with :)

Duane.

bmsgharris
Starting Member

10 Posts

Posted - 2005-10-03 : 10:22:38

Thanks
Graham

Kristen
Test

22859 Posts

Posted - 2005-10-03 : 13:17:04

I've asked this here before and I reckon the best answer that came up with was to create a table, or insert a row into a given table, or similar, when an error occurred.

I actually think that DELETING the row at the start of a section and re-inserting it at the end (i.e. so a total failure of a section will FAIL to create the row) is the most fail-safe:

Something like:

IF EXISTS(SELECT * FROM MyTable WHERE MyColumn = 1234)
BEGIN
DELETE MyTable WHERE MyColumn = 1234
... statements ...
IF NO_ERROR_CONDITION INSERT INTO MyTable(MyColumn) VALUES (1234)
END
GO

... repeat ...

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources