USE
Northwind;
GO
-- Execute these statements in Connection1:
BEGIN
TRAN
UPDATE dbo.Categories
SET CategoryName = 'Drinks'
WHERE CategoryID = 1;
-- Open a new connection and execute this statement
SELECT
CategoryName from dbo.Categories WHERE CategoryID = 1;
-- Create a table to demonstrate compile errors
CREATE
(ID INT PRIMARY KEY,
ColValue CHAR(4));
GO
-- Create the batch
INSERT
INTO dbo.CompileErrors VALUES (1, 'aaaa');
INSERT
INTO dbo.CompileErrors VALUES (2, 'bbbb');
INSERT
INTO dbo.CompileErrors VALUSE (3, 'cccc');
GO
SELECT
* FROM CompileErrors;
GO
INSERT
INTO dbo.CompileErrors VALUES (1, 'aaaa');
GO
INSERT
INTO dbo.CompileErrors VALUES (2, 'bbbb');
GO
INSERT
INTO dbo.CompileErrors VALUSE (3, 'cccc');
GO
-- Insert new rows with PK violation
INSERT
INTO dbo.CompileErrors VALUES (11, 'aaaa');
INSERT
INTO dbo.CompileErrors VALUES (12, 'bbbb');
INSERT
INTO dbo.CompileErrors VALUES (11, 'cccc');
GO
-- Insert new rows with a non-existent table name
INSERT
INTO dbo.CompileErrors VALUES (111, 'aaaa');
INSERT
INTO dbo.CompileErrors VALUES (112, 'bbbb');
INSERT
INTO dbo.CompileErrrs VALUES (113, 'cccc');
GO
-- DROP TABLE CompileErrors;
-- Use @@TRANCOUNT
BEGIN
TRANSACTION;
UPDATE dbo.Employees
SET LastName = 'Da Volio'
WHERE LastName = 'Davolio';
IF @@TRANCOUNT > 0
BEGIN
PRINT N'The transaction is active.';
ROLLBACK TRANSACTION;
PRINT N'The transaction has been rolled back.';
END
GO
SELECT
LastName FROM dbo.Employees;
-- Use @@ERROR
SELECT
message_id, text from sys.messages;
-- Simple error handling
DECLARE
BEGIN
@err int TRANSACTION;
INSERT INTO dbo.Customers (CustomerID, CompanyName)
VALUES ('ALFKI', 'New ALFKI');
SET @Err = @@ERROR
IF @Err = 0
BEGIN
PRINT 'Commit transaction.';
COMMIT
END
ELSE
BEGIN
PRINT 'Error = ' + CAST(@Err AS varchar(10));
ROLLBACK
END
GO
-- Transactions in Stored Procedures
-- Traditional error handling with @@ERROR
CREATE
@CategoryName nvarchar(15) = NULL,
@CategoryID int = NULL OUTPUT,
@ReturnCode int = NULL OUTPUT,
@ReturnMessage nvarchar(255) = NULL OUTPUT
AS
SET
NOCOUNT ON
-- Declare some working variables
DECLARE @Err int;
DECLARE @Rows int;
DECLARE @identity int;
IF @CategoryName IS NULL
BEGIN
SELECT @ReturnCode = 515,
@ReturnMessage = 'Null CategoryName not allowed.'
RETURN
END
SELECT
@ReturnCode AS ReturnCode,
@ReturnMessage AS ReturnMessage;
GO
-- Using RAISERROR
IF
WHERE CategoryName='Seafood')
BEGIN
RAISERROR ('Duplicate CategoryName detected.', 16, 1);
END
GO
-- Creating a user-defined error.
EXECUTE
N'Session ID %d caused an error in %s.';
GO
-- Raise the custom error:
DECLARE
@spid INT;
SET
@spid = @@SPID;
DECLARE
@name NVARCHAR(128);
SET
@name = DB_NAME();
RAISERROR
(50003, 10, 1, @spid, @name)
GO
--EXECUTE sp_dropmessage 50003;
-- TRY/CATCH
BEGIN
TRY
BEGIN TRANSACTION;
INSERT INTO dbo.Customers (CustomerID, CompanyName)
VALUES ('ALFKI', 'New ALFKI');
COMMIT
PRINT 'Transaction committed.'; TRY CATCH
ROLLBACK
PRINT 'Transaction rolled back.'; CATCH
GO
-- Display detailed error information
BEGIN
TRY
BEGIN TRANSACTION;
INSERT INTO dbo.Customers (CustomerID, CompanyName)
VALUES ('ALFKI', 'New ALFKI');
COMMIT
PRINT 'Transaction committed.'; TRY CATCH
ROLLBACK
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.'; CATCH
GO
-- Use RAISERROR in a TRY/CATCH
BEGIN
TRY
RAISERROR ('Error severity 9', 9, 1);
RAISERROR ('Error severity 16', 16, 1); TRY CATCH
DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
SELECT
@ErrorMessage = ('In CATCH block: ' + ERROR_MESSAGE()),
@ErrorSeverity = ERROR_SEVERITY();
RAISERROR (@ErrorMessage,
@ErrorSeverity, 1); CATCH;
GO
-- Create a stored procedure using TRY/CATCH
-- and RAISERROR
CREATE
@CategoryName nvarchar(15) = NULL,
@CategoryID int = NULL OUTPUT,
@ReturnCode int = NULL OUTPUT,
@ReturnMessage nvarchar(255) = NULL OUTPUT
AS
SET
BEGIN
NOCOUNT ON 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
SET @CategoryID=SCOPE_IDENTITY();
SELECT @ReturnCode=0,
@ReturnMessage='Success! ' + @CategoryName + ' added.'; TRY CATCH
-- Test to see if we're in a transaction.
IF @@TRANCOUNT > 0
ROLLBACK
-- 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)); CATCH
GO
-- Execute the Stored Procedure
DECLARE
@CategoryName nvarchar(15)
DECLARE
DECLARE
DECLARE
@CategoryID int @ReturnCode int @ReturnMessage nvarchar(255)
SET
@CategoryName = 'Seafood'
EXECUTE
@CategoryName,
@CategoryID OUTPUT,
@ReturnCode OUTPUT,
@ReturnMessage OUTPUT;
END
IF
WHERE CategoryName=@CategoryName)
BEGIN
SELECT @ReturnCode = 2601,
@ReturnMessage = 'Name already exists.'
RETURN
END TRANSACTION
INSERT INTO dbo.Categories (CategoryName)
VALUES (@CategoryName);
-- Pick up @@ERROR and @@ROWCOUNT
SELECT @Err = @@ERROR, @Rows = @@ROWCOUNT
-- If there's an error, rollback.
IF @Err <> 0
BEGIN
SELECT @ReturnCode=@Err,
@ReturnMessage= 'Transaction rolled back.'
ROLLBACK
RETURN
END
-- If no rows inserted, rollback.
IF @Rows = 0
BEGIN
SELECT @ReturnCode=2601,
@ReturnMessage='Insert failed.'
ROLLBACK
RETURN
END
-- Success! Commit the transaction.
ELSE
BEGIN
COMMIT
SET @identity=SCOPE_IDENTITY();
SELECT @ReturnCode=0, @CategoryID=@identity,
@ReturnMessage='Transaction succeeded, row added.'
RETURN
END
GO
-- Execute the Stored Procedure
DECLARE
@CategoryName nvarchar(15)
DECLARE
DECLARE
DECLARE
@CategoryID int @ReturnCode int @ReturnMessage nvarchar(255)
SET
@CategoryName = 'Seafood1'
EXECUTE
@CategoryName,
@CategoryID OUTPUT,
@ReturnCode OUTPUT,
@ReturnMessage OUTPUT;
BEGIN
SELECT
@ReturnCode AS ReturnCode,
@ReturnMessage AS ReturnMessage;
GO
-- Use XACT_STATE
SET
XACT_ABORT ON;
BEGIN
TRY
BEGIN TRANSACTION;
-- Generate a constraint violation error.
DELETE FROM dbo.Categories WHERE CategoryID=1;
COMMIT TRANSACTION;
PRINT 'Transaction Committed.' TRY CATCH
IF (XACT_STATE()) = -1
BEGIN
DECLARE @category nvarchar(50)
SELECT @Category =
(SELECT CategoryName FROM dbo.Categories WHERE CategoryID=1)
ROLLBACK
PRINT 'The ' + @category + ' category cannot be deleted.';
END CATCH
GO
--CLEANUP
DROP
DROP
PROC dbo.InsertCategoryTransaction;
DROP
PROC dbo.InsertCategoryTryCatch;
EXECUTE
sp_dropmessage 50003; TABLE dbo.CompileErrors;
END
BEGIN
END
END
BEGIN
END
END
BEGIN
END
END
BEGIN
END
END
BEGIN
-- Perform the transaction
-- Exit if CategoryName already exists.
-- Exit if CategoryName is null.
@CategoryID AS CategoryId, dbo.InsertCategoryTryCatch PROCEDURE dbo.InsertCategoryTryCatch sp_addmessage 50003, 16, EXISTS (SELECT CategoryName FROM dbo.Categories @CategoryID AS CategoryId, dbo.InsertCategoryTransaction EXISTS (SELECT CategoryName FROM dbo.Categories PROCEDURE dbo.InsertCategoryTransaction TABLE dbo.CompileErrors
-- Don't execute this yet!!
ROLLBACK