Here are some examples on how to use Try/Catch and RaiseError to address error handling in SQL Server 2008
-- ************************
-- TRY/CATCH Error Handling
-- ************************
BEGIN TRY
BEGIN TRANSACTION;
INSERT INTO dbo.Customers (CustomerID, CompanyName)
VALUES ('ALFKI', 'New ALFKI');
COMMIT TRANSACTION;
PRINT 'Transaction committed.';
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION;
PRINT 'Transaction rolled back.';
END CATCH
GO
-- Display detailed error information
BEGIN TRY
BEGIN TRANSACTION;
INSERT INTO dbo.Customers (CustomerID, CompanyName)
VALUES ('ALFKI', 'New ALFKI');
COMMIT TRANSACTION;
PRINT 'Transaction committed.';
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION;
SELECT
ERROR_NUMBER() AS Number,
ERROR_SEVERITY() AS Severity,
ERROR_STATE() AS State,
ERROR_PROCEDURE() AS procedureName,
ERROR_LINE() AS Line,
ERROR_MESSAGE() AS messageText;
PRINT 'Transaction rolled back.';
END CATCH
GO
-- Use RAISERROR in a TRY/CATCH
BEGIN TRY
RAISERROR ('Error severity 9', 9, 1);
RAISERROR ('Error severity 16', 16, 1);
END TRY
BEGIN CATCH
DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
SELECT
@ErrorMessage = ('In CATCH block: ' + ERROR_MESSAGE()),
@ErrorSeverity = ERROR_SEVERITY();
RAISERROR (@ErrorMessage, @ErrorSeverity, 1);
END CATCH;
GO
-- Create a stored procedure using TRY/CATCH and RAISERROR
CREATE PROCEDURE dbo.InsertCategoryTryCatch
@CategoryName nvarchar(15) = NULL,
@CategoryID int = NULL OUTPUT,
@ReturnCode int = NULL OUTPUT,
@ReturnMessage nvarchar(255) = NULL OUTPUT
AS
SET NOCOUNT ON
BEGIN TRY
-- Test if CategoryName null.
IF @CategoryName IS NULL
RAISERROR ('Validation failed: Null CategoryName.', 16, 1);
-- Test if CategoryName already exists.
IF EXISTS (SELECT CategoryName FROM dbo.Categories
WHERE CategoryName=@CategoryName)
RAISERROR ('Validation failed: Duplicate CategoryName.', 16, 1);
-- Begin the transaction.
BEGIN TRANSACTION
INSERT INTO dbo.Categories (CategoryName)
VALUES (@CategoryName);
COMMIT TRANSACTION;
SET @CategoryID=SCOPE_IDENTITY();
SELECT @ReturnCode=0,
@ReturnMessage='Success! ' + @CategoryName + ' added.';
END TRY
BEGIN CATCH
-- Test to see if we're in a transaction.
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
-- Return error information to client.
SELECT @ReturnCode = ERROR_NUMBER();
SELECT @ReturnMessage = ERROR_MESSAGE() +
' Severity=' + CAST(ERROR_SEVERITY() AS nvarchar(2)) +
'; State=' + CAST(ERROR_STATE() AS nvarchar(2)) +
'; Proc=' + CAST(ERROR_PROCEDURE() AS nvarchar(50)) +
'; Line=' + CAST(ERROR_LINE() AS nvarchar(10));
END CATCH
GO