DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS   SQL 2014 PROGRAMMING   DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to TRY-CATCH in money transfer?

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

Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE