SQL Server Arithmatic Operators

SQL server have several  arithmetic operator for computation and  assignment purpose.  Here is the list of operator  add , subtract multiply, divide , modulo , add assign. 
 

• +     Add     

• -     Subtract     

• *     Multiply     

• /     Divide     

• %     Modulo

• +=    Add Assign

 
Here are some in simple  example of operator uses.

1)Add (+)

DECLARE @value1 DECIMAL(10,2),

                @value2 DECIMAL(10,2)

SELECT @value1=5.1

SELECT @value2=1.1

SELECT [Result_of_Sum]=(@value1+@value2)

Output:

Result_of_Sum

6.20

2)Subtract (-)

 

DECLARE @value1 DECIMAL(10,2),

                @value2 DECIMAL(10,2)

SELECT @value1=5.1

SELECT @value2=1.1

SELECT [Result_of_Substract]=(@value1-@value2)

Output:

Result_of_Substract

4.00

3)Multiply (*)

DECLARE @value1 DECIMAL(10,2),

                @value2 DECIMAL(10,2)

SELECT @value1=5.1

SELECT @value2=1.1

SELECT [Result_of_Substract]=(@value1*@value2)

Output:

Result_of_Multiply

4.00

4)/ Divide

DECLARE @value1 DECIMAL(10,2),

                @value2 DECIMAL(10,2)

SELECT @value1=5.1

SELECT @value2=1.1

SELECT [Result_of_Divide]=(@value1/@value2)

Output:

Result_of_Divide

4.6363636363636

5)% Modulo

DECLARE @value1 INT,

               @value2 INT

SELECT @value1=5

SELECT @value2=2

SELECT [Result_of_Modulo]=(@value1%@value2)

Output:

Result_of_Modulo

1

6)+= Add Assign

DECLARE @value1 VARCHAR(500)

SELECT @value1='Hello'

SELECT @value1+=' World'

SELECT @value1

Output:

Result_of_Modulo

Hello World

 

Below is the example of Arithmetic Operators can be used for large and complex calculation.

Example 1

 

SELECT [Result]=((((5+15)*6)/2)*8)

Output:
480

Example 2

DECLARE @l_val1 INT=3

DECLARE @l_val2 INT=2

DECLARE @l_val3 DECIMAL(10,3)=10.2

DECLARE @l_val4 DECIMAL(10,3)=63.5

DECLARE @l_val5 DECIMAL(10,3)=15.8

DECLARE @l_val6 INT=6

DECLARE @l_val7 INT=7

SELECT [Result]=((((@l_val6+@l_val7)*@l_val5)*@l_val4)/@l_val3)+@l_val1+@l_val2

Result:
1283.715686274

SQL Server Try Catch

             SQL server provide some functionality for implementing error  handling in transaction SQL. Try catch is used for this purpose, it was introduced in 2005 version of SQL server. Try catch is basically two block, the code you want to handle error some exception would be place in try block and if there is any exception occurred in the try block would be cached in the catch block. 

           SQL server try catch block do not have any finally block. It is similar like C# programming language exception handling structure without finally block. 


            Each try block must have immediate followed by a catch block. It is possible for SQL server try catch block in nested format .

1)Below is the syntax of try catch block

BEGIN TRY   

   -- SQL Statements;
END TRY
BEGIN CATCH
        --SQL Statements;
END CATCH

2)Below is example of try catch

BEGIN TRY

         SELECT [Output]=1/0

END TRY

BEGIN CATCH

           SELECT ERROR_MESSAGE() AS [Error Message]

           ,ERROR_PROCEDURE() as [ErrorProcedure]

                      ,ERROR_LINE() AS [ErrorLine]

                     ,ERROR_NUMBER() AS [Error Number]

                     ,ERROR_SEVERITY() AS [Error Severity]

                     ,ERROR_STATE() AS [Error State]

END CATCH

Output 

Error Message ,                     ErrorProcedure,ErrorLine,Error Number ,Error Severity ,Error State
Divide by zero error encountered.    NULL              2            8134           16               1

3)Below is example of nested try catch

BEGIN TRY

         BEGIN TRY

                   SELECT [Output]=1/0

         END TRY

        BEGIN CATCH

                    SELECT  ERROR_MESSAGE() AS [Error Message]

                                 ,ERROR_PROCEDURE() as [ErrorProcedure]

                                 ,ERROR_LINE() AS [ErrorLine]

                                 ,ERROR_NUMBER() AS [Error Number]

                                 ,ERROR_SEVERITY() AS [Error Severity]

                                ,ERROR_STATE() AS [Error State]

             END CATCH

END TRY

BEGIN CATCH

--

END CATCH

4)Below is example of multiple try with single catch

SQL Server Transaction Processing

             SQL Server Transaction Processing. Transaction is used for the system ,where data mismatch is a very sensitive issue. For example  we are doing an ATM transaction, card processing done, currency not been disposed, due to some unavoidable reason , the situation is , the amount is debited from the user account, but the user actually did not get any money from the ATM machine. This kind of situation is not acceptable  in real life transaction. 


             Another example is,  there are two server for a multinational company .Two  server concurrently handle same data, and do load balancing for huge data. When a transaction is done, the first server is updated , the second server need to be update concurrently. If second server does not get updated ,second server will result mismatch of data, the company will face  seviour loss of data.

              Here come the Transaction Processing system. Transaction Processing system guarantee that  the processing either succeed or rollback all. Transaction is atomic unit of processing with two stage ,commit or rollback. It help to retain in ACID property of a  relational databases system. 

Transaction Processing  is mainly used for

• Batch processing
• Separate databases update concurrently
• distributed transaction

Transaction Processing System has four Component

• begin transaction
• end transaction
• commit
• rollback

begin transaction is a beginning of a transaction .
end transaction is the ending of a transaction, if the transaction success , then commit command is executed either to rollback command is executed to rollback all SQL statement.
 

Generally, transaction is kept under Try-catch statement, to detect any error or exception has been occurred during the processing. 
 
Rollback statement is written under the catch blockt to detect any exception has been Occurred or not.


Transaction processing  lock the  data  used by himself. No other transaction can concurrently update or delete the same data, untill the transaction has been completed and it has been released.


There are two type of transaction
  Implicit Transaction : Implicit transaction are two , type DDL and DML. 
DDL are CREATE ,ALTER,DROP,TRUNCATE etc.
DML are INSERT,UPDATE,DELETE delete etc.


Explicit Transactions
 

Below is example of transaction processing. Here student details updated, and with this data, School details are updated. In between,if any exception is occurred, the data will be rollback. 

USE ex311316

GO

Declare @myError varchar(500)

BEGIN TRAN

INSERT INTO school(student_name)

SELECT NAME FROM STUDENT

SELECT @myError = @@ERROR

IF (@myError <> 0) GOTO ERRORBLOCK

COMMIT TRAN

ERRORBLOCK:

IF (@myError <> 0)

BEGIN

PRINT 'Error Occured!'

ROLLBACK TRAN

END

Transaction processing  can be nested. One transaction processing can be nested under another transaction processing.

@@TRANCOUNT return the level of transaction.Untill the inner most 

transaction is committed, the outer transaction does not 
commit. When inner most transaction is committed and successfully 

then outer transaction committed only. 

SELECT 'Staring Transaction', @@TRANCOUNT

BEGIN TRANSACTION TRAN1

INSERT INTO school(student_name)

SELECT NAME FROM STUDENT

-- Second Transaction Start

BEGIN TRANSACTION TRAN2

UPDATE STUDENT_PARENTS

SET PARENT_NAME='Mr.'+PARENT_NAME

WHERE 1=1

COMMIT TRANSACTION TRAN2

-- End Of Second Transaction

SELECT ' Commiting Second Transaction', @@TRANCOUNT

COMMIT TRANSACTION TRAN1

SQL Server IF ELSE

If condition is used for decision making purpose. If condition check Boolean value, if the condition is true then statement under if condition is executed or else controls goes to else part execute this statement. 


You can have more multiple if condition with else statement in a single query. If statement can be written under store procedures , function, Trigger, query for decision making purpose. 


Below is the picture real view of if condition.

1)Below is the syntax of if condition.

IF condition
   {...statements to execute when condition is TRUE...}

[ ELSE
   {...statements to execute when condition is FALSE...} ]

2)Below is the example of if condition.

Declare @myValue INT=10

IF (@myValue < 25)

          PRINT 'Value_1';

ELSE

          PRINT 'Value_2';

3)Here is the example of if condition with multiple if.

 

Declare @myValue INT=10

IF (@myValue =10)

          PRINT 'Value 10';

ELSE IF (@myValue =15)

          PRINT 'Value 15';

ELSE IF (@myValue =20)

         PRINT 'Value 20';

ELSE IF (@myValue =22)

         PRINT 'Value 22';

ELSE

         PRINT 'Value_2';