How can I implement error handling within a SQL script that uses b

I am trying to incorporate error handling into a SQL script that creates
several database objects.
Those objects are referenced later in the script by other blocks of code.
It is my understanding that I need to use a new batch to reference an object
that was just created. I can do this by inserting a GO statement after the
object creation statement.
But how do I implement error handling within a SQL script that uses batches?
IOW I don't want a subsequent batch to execute if a previous batch failed.
If I didn't have separate batches in my script I could use a RETURN or GOTO
statement to handle errors. But with batches I have the following problem:
--use RETURN
PRINT '1'
GO
PRINT '2'
RETURN
GO
PRINT '3'
--OUTPUT
1
2
3
--use GOTO
PRINT '1'
GO
PRINT '2'
GOTO error
GO
PRINT '3'
ERROR:
PRINT 'ERROR'
--OUTPUT
1
Server: Msg 133, Level 15, State 1, Line 2
A GOTO statement references the label 'error' but the label has not been
declared.
3
ERROR
Is the only solution to break up the script to run as indivifual batchs and
then evaluate each one after execution?Use the
IF EXISTS(SELECT 1 FROM dbo.sysobjects WHERE
id=object_id(@.v_objectname))
BEGIN
-- start processing
END
ELSE
BEGIN
PRINT @.v_objectname + ' was not created'
END|||Do you need a new batch to reference an object created in the current bacth?
try this
create table t1 (A int)
insert into t1 values(1)
select * from t1
drop table t1
Or did I misunderstand u?|||On Wed, 31 May 2006 10:36:02 -0700, Dave wrote:
(snip)
>But how do I implement error handling within a SQL script that uses batches
?
> IOW I don't want a subsequent batch to execute if a previous batch failed.[/color
]
Hi Dave,
There's no good answer for this. Since the cient sends just one batch to
the server, waits for it to execute, then sends the other one, there is
no simple way to have an error in batch #1 abort batch #2.
There are kludges, though. The easiest (but nastiest) is to use a
RAISERROR with a severity of at least 20 - those are considered fatal,
and prompt SQL Server to terminate the connection after sending the
error message.
Somewhat more work (but still the version I'd prefer) is to use a table
to carry over status information. This can be a temp table. For
instance:
-- Preparational steps
CREATE TABLE #Status (Status varchar(30) NOT NULL)
INSERT INTO #Status (Status) VALUES ('Okay')
go
-- Start of batch #1
(Do something)
(Do some more)
IF (something went wrong)
BEGIN
UPDATE #Status
SET Status = 'Error in batch #1'
END
go
-- Start of batch #2
IF (SELECT Status FROM #Status) = 'Okay'
BEGIN
(Do something)
(Do some more)
IF (something went wrong)
BEGIN
UPDATE #Status
SET Status = 'Error in batch #2'
END
END
go
-- Repeat for batches #3, #4, ...
-- After last batch:
IF (SELECT Status FROM #Status) <> 'Okay'
BEGIN
PRINT 'Error'
SELECT Status FROM #Status
END
DROP TABLE #Status
Hugo Kornelis, SQL Server MVP