|
Execute the following
SQL Server T-SQL example scripts in Management Studio Query Editor to create and execute a stored procedure for banking try-catch error control demonstration.
-- SQL Server TRY CATCH - T-SQL try catch error handling - SQL stored procedure
USE tempdb
GO
SET NOCOUNT ON
-- Balance column has check constraint - SQL check constraint
CREATE TABLE CheckingAccount (
CustomerID INT IDENTITY ( 1 , 1 ),
Balance MONEY,
CHECK ( Balance >= 0 ),
TranDate datetime default(getdate()))
CREATE TABLE SavingAccount (
CustomerID INT IDENTITY ( 1 , 1 ),
Balance MONEY,
CHECK ( Balance >= 0 ),
TranDate datetime default(getdate()))
GO
INSERT CheckingAccount(Balance) SELECT 2000
INSERT CheckingAccount(Balance) SELECT 1000
INSERT CheckingAccount(Balance) SELECT 3000
INSERT CheckingAccount(Balance) SELECT 1000
INSERT CheckingAccount(Balance) SELECT 3500
INSERT CheckingAccount(Balance) SELECT 1500
INSERT SavingAccount(Balance) SELECT 3000
INSERT SavingAccount(Balance) SELECT 6000
INSERT SavingAccount(Balance) SELECT 2000
INSERT SavingAccount(Balance) SELECT 5000
INSERT SavingAccount(Balance) SELECT 2300
INSERT SavingAccount(Balance) SELECT 5300
GO
SELECT * FROM SavingAccount ORDER BY CustomerID
/*
CustomerID Balance TranDate
1 3000.00 2019-02-25 10:05:35.170
2 6000.00 2019-02-25 10:05:35.187
3 2000.00 2019-02-25 10:05:35.187
4 5000.00 2019-02-25 10:05:35.187
5 2300.00 2019-02-25 10:05:35.187
6 5300.00 2019-02-25 10:05:35.187
*/
DROP PROCEDURE uspTransferFromSavingsToCheckingAccount
GO
CREATE PROCEDURE uspTransferFromSavingsToCheckingAccount
@CustomerID INT,
@Amount MONEY
AS
BEGIN
SET XACT_ABORT ON
SET NOCOUNT ON
BEGIN TRY
BEGIN TRAN
UPDATE CheckingAccount
SET Balance = Balance + @Amount
WHERE CustomerID = @CustomerID
UPDATE SavingAccount
SET Balance = Balance - @Amount
WHERE CustomerID = @CustomerID
PRINT 'TRANSFER SUCCESSFUL.'
COMMIT
END TRY
BEGIN CATCH
ROLLBACK
PRINT 'INSUFFICIENT AMOUNT IN SAVINGS.'
END CATCH
END
GO
-- Execute stored procedure - Insufficient balance to do transfer
-- Check constraint will be violated - CATCH will kick in
EXEC uspTransferFromSavingsToCheckingAccount 3 , 3000
GO
/* Messages
INSUFFICIENT AMOUNT IN SAVINGS.
*/
-- Sufficient balance to do transfer
SELECT Balance FROM SavingAccount WHERE CustomerID=3
-- 2000.00
SELECT Balance FROM CheckingAccount WHERE CustomerID=3
-- 3000.00
EXEC uspTransferFromSavingsToCheckingAccount 3 , 2000
SELECT Balance FROM SavingAccount WHERE CustomerID=3
-- 0.00
SELECT Balance FROM CheckingAccount WHERE CustomerID=3
-- 5000.00
GO
/* Messages
TRANSFER SUCCESSFUL.
*/
-- Cleanup
DROP TABLE CheckingAccount
DROP TABLE SavingAccount
GO |
