BlackWaspTM

This web site uses cookies. By using the site you accept the cookie policy.This message is for compliance with the UK ICO law.

SQL Server
SQL 2005+

SQL Server Arithmetic Operators

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.

Arithmetic Operators

So far in the SQL Server Programming Fundamentals tutorial we have looked at the basic tasks of creating a database schema, adding and maintaining data, performing queries and creating simple stored procedures. These tasks have been looked at individually and have been introduced in stages. The previous articles have introduced concepts and techniques that are essential to developers that use SQL Server but have included little actual programming elements.

In the next group of articles in the series we will examine some of the operators, functions and code structures that allow you to program directly within a SQL Server environment. These can be used within queries or update statements, or directly from SQL Server Management Studio. They can also be included in stored procedures, where they can become extremely powerful.

In this article we will investigate the use of arithmetic operators in various scenarios. SQL Server provides five such operators, including additional, subtraction, multiplication and division.

Using Arithmetic Operators

Some of the examples in this article use data from the JoBS database. This is a database that has been generated and populated with data throughout the course of this tutorial. If you do not have an up-to-date copy of the database you can create one by downloading and executing the script provided via the link at the top of this page.

Basic Operators

The first four arithmetic operators are available in almost all programming languages. These are addition (+), subtraction (-), multiplication (*) and division (/). Using a new query window in SQL Server Management Studio we can demonstrate their use with the PRINT command. This command simple outputs information to the results pane. Execute the following to see the results:

PRINT 10 + 4    -- 14
PRINT 10 - 4    -- 6
PRINT 10 * 4    -- 40
PRINT 10 / 4    -- 2

The last of the four results is worthy of comment. In this case, SQL Server has assumed that the values being used are integers and so the result is also an integer. This has caused the value to be rounded downwards from 2.5 to 2. It is important to understand that all arithmetic operation results are limited by the data types that are being used. We will investigate conversion between data types in a later article in this tutorial. For now, we can modify the final PRINT statement to include a value with a decimal point for a different result:

PRINT 10.0 / 4    -- 2.500000

The fifth numeric operator is modulus (%). This returns the remainder value for an integer division. For example, if you divide ten by six, the integer part of the result would be one and the remainder would be four:

PRINT 10 % 6    -- 4

Operators and Queries

The arithmetic operators can be used within queries wherever the result that they give would be valid. This includes operations within the selected column list and the WHERE clause. The operators can be used with literal values, column values and variables. As with other functions that we have examined earlier in the tutorial, when you calculate the value of a column it will lose the column name. You can create an alias for the new column using the AS clause.

Try executing the query below. This query lists all of the engineers from the JoBS database and includes a column that gives their daily pay. This value is calculated for each engineer by multiplying their hourly rate by eight.

SELECT
    EngineerName,
    HourlyRate,
    OvertimeRate,
    HourlyRate * 8 AS DailyRate
FROM
    Engineers

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
15 August 2009