“I do not use Transactions in SQL as I use Stored Procedure“.

I just realized that there are so many misconceptions about this subject. Transactions has nothing to do with Stored Procedures. Let me demonstrate that with a simple example.

USE tempdb
GO
-- Create 3 Test Tables
CREATE TABLE TABLE1 (ID INT);
CREATE TABLE TABLE2 (ID INT);
CREATE TABLE TABLE3 (ID INT);
GO
-- Create SP
CREATE PROCEDURE TestSP
AS
INSERT INTO TABLE1 (ID)
VALUES (1)
INSERT INTO TABLE2 (ID)
VALUES ('a')
INSERT INTO TABLE3 (ID)
VALUES (3)
GO
-- Execute SP
-- SP will error out
EXEC TestSP
GO
-- Check the Values in Table
SELECT *
FROM TABLE1;
SELECT *
FROM TABLE2;
SELECT *
FROM TABLE3;
GO

Now, the main point is: If Stored Procedure is transactional then, it should roll back complete transactions when it encounters any errors. Well, that does not happen in this case, which proves that Stored Procedure does not only provide just the transactional feature to a batch of T-SQL.

Let’s see the result very quickly.

It is very clear that there were entries in table1 which are not shown in the subsequent tables. If SP was transactional in terms of T-SQL Query Batches, there would be no entries in any of the tables. If you want to use Transactions with Stored Procedure, wrap the code around with BEGIN TRAN and COMMIT TRAN.

The example is as following.
CREATE PROCEDURE TestSPTran
AS
BEGIN TRAN
INSERT INTO TABLE1 (ID)
VALUES (11)
INSERT INTO TABLE2 (ID)
VALUES ('b')
INSERT INTO TABLE3 (ID)
VALUES (33)
COMMIT
GO
-- Execute SP
EXEC TestSPTran
GO
-- Check the Values in Tables
SELECT *
FROM TABLE1;
SELECT *
FROM TABLE2;
SELECT *
FROM TABLE3;
GO
-- Clean up
DROP TABLE Table1
DROP TABLE Table2
DROP TABLE Table3
GO

Error Converting Data Type Money to Decimal

Run following script and you will see that it will thrown an error.
DECLARE @mymoney MONEY;
SET @mymoney = 12345.67;
SELECT CAST(@mymoney AS DECIMAL(5,2)) MoneyInt;
GO

The datatype of money is also visually look similar to the decimal, 

why it would throw following error:

Msg 8115, Level 16, State 8, Line 3
Arithmetic overflow error converting money to data type numeric.

Solution:

DECLARE @mymoney MONEY;
SET @mymoney = 12345.67;
SELECT CAST(@mymoney AS DECIMAL(7,2)) MoneyInt;



 
Explanation: 
you have 7 digits in @mymoney (including decimals) 
5, 2 means that it’s 5 digits in total with 2 after the decimal point. 
To represent the number you show, it needs a total of 7 with 2 
after the decimal place and 5 before.