Chandradev's Blog

CREATE PROCEDURE DeleteEmployee ( @EmployeeID int )
AS

BEGIN TRY
BEGIN TRANSACTION    — Start the transaction

— Delete the Employee’s phone numbers
DELETE FROM EmployeePhoneNumbers
WHERE EmployeeID = @EmployeeID

— Delete the Employee record
DELETE FROM Employees
WHERE EmployeeID = @EmployeeID

— If we reach here, success!
COMMIT
END TRY
BEGIN CATCH
— Whoops, there was an error
IF @@TRANCOUNT > 0
ROLLBACK

— Raise an error with the details of the exception
DECLARE @ErrMsg nvarchar(4000), @ErrSeverity int
SELECT @ErrMsg = ERROR_MESSAGE(),
@ErrSeverity = ERROR_SEVERITY()

RAISERROR(@ErrMsg, @ErrSeverity, 1)
END CATCH

We can also write transaction like this

CREATE PROCEDURE TransferMoney
(
@Account1 Int,
@Account2 Int,
@myValue Numeric(18),
@myValue2 Numeric(18)
)
AS

— STEP 1: Start the transaction

BEGIN TRANSACTION
— STEP 2 & 3: Issue the Insert statement, checking @@ERROR after each statement

Insert Into Bank (Balance) Values (@MyValue)
WHERE AccountID = @Account1

IF @@ERROR = 0
BEGIN
Insert Into Bank (Balance) Values (@MyValue2)
WHERE AccountID = @Account2

IF @@ERROR = 0
BEGIN
COMMIT TRANSACTION
END
ELSE
BEGIN
— Rollback the transaction
ROLLBACK TRANSACTION
END
END
ELSE
BEGIN
ROLLBACK TRANSACTION
END
Return