SQL 2005+

SQL Server Arithmetic Operators (2)

The thirty-second part of the SQL Server Programming Fundamentals tutorial describes the basic arithmetic operators provided by Transact-SQL (T-SQL). These operators allow simple mathematical operations within queries, procedures and other statements.

Operators and Variables

The arithmetic operators can be used with values held in variables as if they were literal values. The resultant values can also be stored in variables if required. The following, simple example declares three integer variables and assigns literal values to the first two. The sum of these two variables is then calculated, assigned to a third variable and outputted.

DECLARE @A INT, @B INT, @C INT
SET @A = 10
SET @B = 5
SET @C = @A + @B
PRINT @C

Operators and Stored Procedures

Each of the above examples could be incorporated into code within a stored procedure, possibly with variables supplied as parameters of the procedure. This permits powerful, reusable units of code to be created and held within the database. The results of the calculations could be used internally within the procedure or returned via the results of a query or the procedure's return value.

The possibilities when using arithmetic and other operators are endless. However, as a simple example consider the following stored procedure definition:

CREATE PROCEDURE GetEngineerBonusRates (
    @RateMultiplier DECIMAL(3,2)
) AS
BEGIN
    SET NOCOUNT ON

    SELECT
        EngineerName,
        HourlyRate,
        HourlyRate * @RateMultiplier AS BonusRate
    FROM
        Engineers
END

This simple procedure retrieves all of the engineer names and their standard hourly rates. The rate multiplier value, provided as a parameter, is used in the calculation of a bonus rate that each engineer may receive for jobs when expectations are exceeded. To run the procedure with a rate multiplier of 1.25, execute the following:

GetEngineerBonusRates 1.25

Unary Operators

The addition and subtraction operators can be used as unary operators, meaning that only one operand is required. By prefixing a value with the subtraction operator the value is negated. Using a prefix of "+" has no effect but can enhance the clarity of code in some situations:

PRINT -5    -- -5
PRINT +5    -- 5