Menu
I have a sql file containing some sql scripts (DDL and DML) which I execute by calling from the windows command line. The issue is when an error occurs it does report the error but all the sql statements in the file are still executed, whereas i want that as soon as the first error is encountered in one of the sql statements , the execution should stop right there.
I am using sql server as my DB
Following is the sample of my script
CREATE TABLE #tmpErrors (Error int)
GO
SET XACT_ABORT ON
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
BEGIN TRANSACTION
GO
/** Main Scripts **/
PRINT N'Creating [dbo].[Student]'
GO
CREATE TABLE [dbo].[Student](
[Id] [bigint] NOT NULL,
[Subject] [varchar](15) NOT NULL,
CONSTRAINT [PK_Student] PRIMARY KEY CLUSTERED
(
[Id] ASC
)
)
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Adding [StudentID] column to [dbo].[School]'
GO
ALTER TABLE [dbo].[School] ADD [StudentID] [bigint] NULL
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
/***And many other DDL and DML statements, each followed by an error check***/
/**
* Main teardown
*/
IF EXISTS (SELECT * FROM #tmpErrors) ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT>0 BEGIN
PRINT 'The database update succeeded'
COMMIT TRANSACTION
END
ELSE PRINT 'The database update failed'
GO
IF EXISTS (SELECT * FROM #tmpErrors) OR (@@ERROR<>0)
BEGIN
RAISERROR (N'An error was encountered', 20, 1 ) WITH LOG, NOWAIT, SETERROR SELECT @@ERROR AS error_number
END
GO
/**
* Final teardown
*/
DROP TABLE #tmpErrors
GO
215
Just use a RETURN (it will work both inside and outside a stored procedure). In a script, you can't do a RETURN with a value like you can in a stored procedure, but you can do a RETURN. dangerous to assume as it will continue after then next GO. GO is a script terminator or delimiter; it's not SQL code.
RaisError does not end processing of a batch. All you need to do is put a Return after the RaisError and the batch will stop there. Errors with a severity of 20 or higher stop the transaction and cause an immediate disconnect.
Using ApexSQL Refactor, all SQL statements can be terminated with a semicolon to prevent syntax mistakes in future versions of SQL Server and format SQL code and scripts to comply with the SQL formatting rules.
In SQL Server you can take advantage of TRY... CATCH statements to handle errors. When writing code that handles errors, you should have a TRY block and a CATCH block immediately after it. The TRY block starts with a BEGIN TRY statement and ends with an END TRY statement.
I'm pretty sure BEGIN TRY and BEGIN CATCH will stop execution when an error is hit and take the execution straight to the error handler:
BEGIN TRY
' do stuff
END TRY
BEGIN CATCH
'handle
END CATCH
Edit: here's an example:
BEGIN TRY
DECLARE @int int
SET @int = 1
SET @int = 1 / 0
SET @int = 2
SELECT 'Everything OK'
END TRY
BEGIN CATCH
SELECT 'Oops'
END CATCH
Comment out the divide by zero line above to see 'Everything OK', otherwise you will see 'Oops' in the resultset
152
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
