When structuring scripts to execute in production, I often wrap a transaction around the contents of my statements. This is done to keep data integrity in-tact and to make things simpler to support in the event that the execute fails. Here is a quick example:
BEGIN TRANSACTION
UPDATE tbl_Employees SET LastName = 'Williams' WHERE Id = 100
UPDATE tbl_EmployeeDepartments SET EmployeeId = 100 WHERE Id = 20
COMMIT TRANSACTION
In this example, I’ve wrapped both UPDATE statements inside a transaction because I only want the data to be committed when both tables are updated successfully. If there were errors in either statement (column constraint issues, etc.), the transaction would remain open and I would have to issue a ROLLBACK TRANSACTION statement to proceed.
To take this to the next level, I make use of TRY/CATCH and will automatically issue a ROLLBACK in case of a statement execution failure.
Consider the scenario:
CREATE TABLE [dbo].[tbl_Employees](
[EmployeeId] [int],
[FirstName] [varchar](150)
[LastName] [varchar](150)
)
CREATE TABLE [dbo].[tbl_EmployeeDepartments](
[DepartmentId] [int],
[EmployeeId] [int]
)
INSERT INTO tbl_Employees VALUES (100, 'Reagan', 'Williams')
INSERT INTO tbl_Departments VALUES (20, 100)
BEGIN TRANSACTION
BEGIN TRY
-- This will succeed by itself
UPDATE tbl_Employees SET LastName = 'W' WHERE EmployeeId = 100
-- This will fail due to the datatype
UPDATE tbl_EmployeeDepartments SET EmployeeId = 'Williams' WHERE DepartmentId = 20
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
END CATCH
The query returns a successful execution, even though the 2nd update statement inside the BEGIN/TRY block failed.
The CATCH coupled with the “ROLLBACK TRANSACTION” is what causes this query to execute without error. This is where the RAISERROR function can be used to help identify query problems.
The RAISERROR function allows a string to be passed in the first argument as a message:
RAISERROR('An exception was encountered.', 15, 1)
But I would suggest making use of the functions:
ERROR_MESSAGE()
ERROR_SEVERITY()
ERROR_STATE()
As these can return more meaningful error messages.
Example of how to successfully trap the error message and report it to the results window (for unattended script executions):
DECLARE @ErrorMessage varchar(4000)
DECLARE @ErrorSeverity int
DECLARE @ErrorState int
SET @ErrorState = 0
BEGIN TRANSACTION
BEGIN TRY
-- This will succeed by itself
UPDATE tbl_Employees SET LastName = 'W' WHERE EmployeeId = 100
-- This will fail due to the datatype
UPDATE tbl_EmployeeDepartments SET EmployeeId = 'Williams' WHERE DepartmentId = 20
END TRY
BEGIN CATCH
SELECT
@ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE()
END CATCH
IF @ErrorState != 0
BEGIN
IF XACT_STATE() != 0
BEGIN
ROLLBACK TRANSACTION
END
RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState)
END
ELSE
BEGIN
IF XACT_STATE() = 1
BEGIN
COMMIT TRANSACTION
END
END
Here is the results output of the SQL Server Agent log:
You can read more about RAISERROR here:
http://msdn.microsoft.com/en-us/library/ms178592.aspx